SolvedPrivate

help with formula to extract data based on condition

Posted on 2015-02-05
5
23 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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 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

9 Experts available now in Live!

Get 1:1 Help Now