Go Premium for a chance to win a PS4. Enter to Win

x
?
SolvedPrivate

help with formula to extract data based on condition

Posted on 2015-02-05
5
Medium Priority
?
32 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
5 Comments
 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 800 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 23

Accepted Solution

by:
Ejgil Hedegaard earned 1200 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 34

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 34

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

926 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