SolvedPrivate

help with formula to extract data based on condition

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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