SolvedPrivate

help with formula to extract data based on condition

Posted on 2015-02-05
5
29 Views
Last Modified: 2016-02-10
hello,  

please see attached file.

in the example uploaded, there are two sheets, Data and Extract
in the Extract sheet, I want to put a formula that extracts all rows and puts them in sheet Extract if the Column A of Data sheet cells contain a string *Manager*

see Extract sheet that is how the data should look like after put the formulas there.

I know how to do this with auto filter and copy and paste in there. but is there any way to get this done by formula?  if formula is not possible at all then VBA

thanks.
Book1.xlsx
0
Comment
Question by:Flora
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 200 total points
ID: 40592264
It's not possible to do this with a formula, but VBA is fairly straight forward...

Sub GetManagers()
    With Worksheets("Data")
        .Range("A1:G1").AutoFilter
        .UsedRange.AutoFilter Field:=1, Criteria1:="=*Manager*"
        .Range(.Range("A1:G1"), .Range("A1:G1").End(xlDown)).Copy Worksheets("Extract").Range("A1")
        .Range("A1:G1").AutoFilter
    End With
End Sub

Open in new window

0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 300 total points
ID: 40592511
With a couple of extra columns to identify the rows to extract, it is possible to do with formulas.
See Index column H on Sheet1, and Index column A on Sheet3.
Extract in column B to H on Sheet3 by the Index function.
Extract-managers.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40593327
You can also use Advanced Filter rather than Auto Filter.

The three requirements for this are:

Data Table - Your Data sheet
Criteria - A separate small table with headers from the Data table and values below. In your case it would the header from column A and in cell immediately below header - *MANAGER*
Copy to Range - This would be your Extract sheet but you only need to specify the first row, ie the headers. The headers on this sheet must be the same as the headers on the data sheet but you don't have to include all of them or be in the same order as the Data sheet.

To do the Filter,
1) Setup the Criteria table as described above off to one side of the Data table or elsewhere.
2) Ensure headers on Extract sheet are the same as the Data sheet.
3) Select a cell in blank area on the Extract sheet and go to Data tab and click Advanced, next to the big Filter button.
4) Tick the Radio button for Copy to another location
5) Complete the three parameters, selecting the range selector buttons; for the copy to range just select the headers on the Extract sheet.

Click OK. The Extract will overwrite the data on the Extract sheet but only to the extent that the new extract requires, ie if there are less rows than previously copied, the lower rows won't be overwritten. If this can be an issue, delete the extracted data before running the Filter; ensure headers are left in place.

Hope that is clear.

Thanks
Rob H
0
 
LVL 6

Author Closing Comment

by:Flora
ID: 40593331
Wayne, Thanks very much for the VBA solution.

Ejgil , Thanks very much for the formula solution.

i can use both.

you guys are stars !

wish you guys a very good weekend.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40593337
Looks like you might have missed the Advanced Filter suggestion.

VBA will mean enabling of macros and saving as xlsm; formulas will need to be copied down as required or fill an excessive range to start with which could potentially impact resources.

Thanks
Rob H
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question