SolvedPrivate

help with formula to extract data based on condition

Posted on 2015-02-05
5
24 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 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 21

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 32

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 5

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

862 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now