2 Pass Data Extract Macro Request
EE Pros,
Each day I get a new/updated dataset in a new WS (all columns stay the same). I manually cut/paste the new data over the old data (number of rows/records may change up or down).
My thought is if I have it in a range name (OriginalData) and that the range would automatically expand or contract to accommodate the new/updated data records.
Then I think I need a Macro to extract 2 subsets of data that requires two passes. The first pass simply identifies a data element in a particular column. It may be able to be set up using Filtering (or Advanced Filtering) to Identify the particular selection (YELLOW in my Example). The first pass result is then copied and pasted into Sheet2 with a Data Range (ResultsData).
The second pass is a little more complicated. I have to take the remaining data (i.e. that data that is left over when the records/rows that are selected in the first pass that are extracted) and do a text search using two text strings on a different column (BLUE in my Example). The results are then placed in Sheet2 as additional record/row results.
I have put together a good example of what I'm looking for. I believe that a macro that defines an initial range (Originaldata) and an output range (ResultsData) along with a way to automate the two passes and place the result in a separate sheet (sheet2) would do the trick.
Much thanks in advance.
B.
D--Data-Data-Temp-Data-Extract.xlsm
Each day I get a new/updated dataset in a new WS (all columns stay the same). I manually cut/paste the new data over the old data (number of rows/records may change up or down).
My thought is if I have it in a range name (OriginalData) and that the range would automatically expand or contract to accommodate the new/updated data records.
Then I think I need a Macro to extract 2 subsets of data that requires two passes. The first pass simply identifies a data element in a particular column. It may be able to be set up using Filtering (or Advanced Filtering) to Identify the particular selection (YELLOW in my Example). The first pass result is then copied and pasted into Sheet2 with a Data Range (ResultsData).
The second pass is a little more complicated. I have to take the remaining data (i.e. that data that is left over when the records/rows that are selected in the first pass that are extracted) and do a text search using two text strings on a different column (BLUE in my Example). The results are then placed in Sheet2 as additional record/row results.
I have put together a good example of what I'm looking for. I believe that a macro that defines an initial range (Originaldata) and an output range (ResultsData) along with a way to automate the two passes and place the result in a separate sheet (sheet2) would do the trick.
Much thanks in advance.
B.
D--Data-Data-Temp-Data-Extract.xlsm
You can set a Named Range with a dynamic range using the OFFSET function in the RefersTo input of the Name Manager.
=OFFSET(Data!A1,0,0,COUNTA
This will create a range on the Data tab starting at A1 and extending down the number of entries in column A and across 12 columns.
I think you can do the extraction in one sweep with Advanced Filter with the following criteria:
Yellow column = Specific Element OR Blue columns contains one of two text entries.
The criteria table for the Adv Filter would look something like:
Yellow Header Blue Header
Specific Element
Text1
Text2
Would that be true?
Thanks
Rob H
=OFFSET(Data!A1,0,0,COUNTA
This will create a range on the Data tab starting at A1 and extending down the number of entries in column A and across 12 columns.
I think you can do the extraction in one sweep with Advanced Filter with the following criteria:
Yellow column = Specific Element OR Blue columns contains one of two text entries.
The criteria table for the Adv Filter would look something like:
Yellow Header Blue Header
Specific Element
Text1
Text2
Would that be true?
Thanks
Rob H
ASKER
Phillip,
I opened your workbook up and see you have created a Macro. When I run it I get a debug error. Can you check it out?
Rob,
I'm just becoming familiar with Advanced Filtering but if I understand how it works correctly, you may be correct. Can you take the Example and create what you mean as the use case and I can take a look?
Thank you both,
B.
I opened your workbook up and see you have created a Macro. When I run it I get a debug error. Can you check it out?
Rob,
I'm just becoming familiar with Advanced Filtering but if I understand how it works correctly, you may be correct. Can you take the Example and create what you mean as the use case and I can take a look?
Thank you both,
B.
> I believe that a macro that defines an initial range (Originaldata)
Select your table (cells A5:E17) before running the macro.
Select your table (cells A5:E17) before running the macro.
See attached.
I have added copies of your sheets to show the results.
On Original Data (2) I have created a dynamic Named Range "OriginalData2" using the OFFSET function as previously suggested, look at the Name Manager to see this. Also on this sheet have moved the Criteria range off to the right so that it didn't mess up the dynamic range, then called this range Criteria2. This is now on 3 rows so that the filter sees the extract as Heading 2 equal to ABC OR Heading4 equal to QPM or OAP.
Results Data (2) has a Named Range "ResultsData2" which is just the headings on that sheet.
To run the Advanced Filter go to the results sheet and select a cell in a blank area. Start the Advanced Filter wizard and enter criteria as per screen shot below:
Then click OK. The result will overwrite the contents of the existing Result Data. If current extract has more records than previous extract, all of previous extract will be overwritten. If current has less than previous, only those rows for the current extract will be overwritten leaving the remainder of the previous extract. If the latter could occur, it would be best to delete previous extract before running; ensure headings are not deleted.
Hope this makes sense.
Thanks
Rob H
D--Data-Data-Temp-Data-Extract.xlsm
I have added copies of your sheets to show the results.
On Original Data (2) I have created a dynamic Named Range "OriginalData2" using the OFFSET function as previously suggested, look at the Name Manager to see this. Also on this sheet have moved the Criteria range off to the right so that it didn't mess up the dynamic range, then called this range Criteria2. This is now on 3 rows so that the filter sees the extract as Heading 2 equal to ABC OR Heading4 equal to QPM or OAP.
Results Data (2) has a Named Range "ResultsData2" which is just the headings on that sheet.
To run the Advanced Filter go to the results sheet and select a cell in a blank area. Start the Advanced Filter wizard and enter criteria as per screen shot below:
Then click OK. The result will overwrite the contents of the existing Result Data. If current extract has more records than previous extract, all of previous extract will be overwritten. If current has less than previous, only those rows for the current extract will be overwritten leaving the remainder of the previous extract. If the latter could occur, it would be best to delete previous extract before running; ensure headings are not deleted.
Hope this makes sense.
Thanks
Rob H
D--Data-Data-Temp-Data-Extract.xlsm
ASKER
OK Guys.... you are teaching me a lot here.
Phillip, I love the ease of the Macro....but I cannot figure out;
1.) Why doesn't it simply look at the Range Name (OriginalData), which defines the new data I cut and paste into the Range, instead of having me highlight all the Cells?
2.) When I go to the results Tab, I'm not getting the results I expected. When I select XYZ instead of ABC, I still get ABC, but with additional rows from XYZ. I'm not sure this is working right. Can you put in commentary as to the additional tables below the Original Data that are now part of the WS? Would those get auto created when I run the macro or do I have to add something?
Ron, I can't get the filter approach to work as I change the criteria (Criteria2). And don't you mean; "Heading4 equal to QPM AND OAP"?
B.
Phillip, I love the ease of the Macro....but I cannot figure out;
1.) Why doesn't it simply look at the Range Name (OriginalData), which defines the new data I cut and paste into the Range, instead of having me highlight all the Cells?
2.) When I go to the results Tab, I'm not getting the results I expected. When I select XYZ instead of ABC, I still get ABC, but with additional rows from XYZ. I'm not sure this is working right. Can you put in commentary as to the additional tables below the Original Data that are now part of the WS? Would those get auto created when I run the macro or do I have to add something?
Ron, I can't get the filter approach to work as I change the criteria (Criteria2). And don't you mean; "Heading4 equal to QPM AND OAP"?
B.
The macro is provided for you to adjust as you wish,
Criteria2 range was set manually covering just the five headings and three rows. If you change the criteria make sure you adjust the range and then rerun the Adv Filter wizard.
The criteria work as follows:
1) one entry under one header = return entries that match that single entry
2) multiple entries under one header = return entries that match one of the entries
3) one or more entries under different headings on the same row = match entry from one heading AND match entry under other heading
4) one or more entries under different headings on different rows = match entry from one heading OR match entry for other heading
There are other options but these are the basics.
Thanks
Rob
The criteria work as follows:
1) one entry under one header = return entries that match that single entry
2) multiple entries under one header = return entries that match one of the entries
3) one or more entries under different headings on the same row = match entry from one heading AND match entry under other heading
4) one or more entries under different headings on different rows = match entry from one heading OR match entry for other heading
There are other options but these are the basics.
Thanks
Rob
And yes I did mean heading4, must have been a typo.
ASKER
Ron,
Here is the reassembled WB with your recommendations. If you change the criteria, for some reason, when you run the Advanced Filtering with the right Range Names, you don't get the right results. Can you take a look?
B.
Data-Extract-Using-Advanced-Filteringv3.
Here is the reassembled WB with your recommendations. If you change the criteria, for some reason, when you run the Advanced Filtering with the right Range Names, you don't get the right results. Can you take a look?
B.
Data-Extract-Using-Advanced-Filteringv3.
Who's Ron? Last couple of posts you have addressed to Ron; assuming they are aimed at me.
Rob
Rob
In the file you have uploaded, the Criteria range is set to O1:S4 but there is only criteria entries in row 2, rows 3 & 4 being blank.
The Advanced Filter is reading the blank rows as "match anything"; hence why the results are showing everything.
If you change the Criteria Range to be only O1:S2, the result is just three entries with XYZ under Heading2.
Maybe need to set up some sort of dynamic range for the criteria as well.
Thanks
Rob
The Advanced Filter is reading the blank rows as "match anything"; hence why the results are showing everything.
If you change the Criteria Range to be only O1:S2, the result is just three entries with XYZ under Heading2.
Maybe need to set up some sort of dynamic range for the criteria as well.
Thanks
Rob
Dynamic Range for criteria:
Alongside the criteria rows (column T) put this formula:
=COUNTA(O1:S1) and copy down several rows, probably worth going excessive down to 20 or something.
Then change the Refers To criteria for the Range Criteria to:
=OFFSET(Original!$O$1,0,0,
This will give a range that is 5 columns wide and as many rows deep as required.
Thanks
Rob H
Alongside the criteria rows (column T) put this formula:
=COUNTA(O1:S1) and copy down several rows, probably worth going excessive down to 20 or something.
Then change the Refers To criteria for the Range Criteria to:
=OFFSET(Original!$O$1,0,0,
This will give a range that is 5 columns wide and as many rows deep as required.
Thanks
Rob H
ASKER
Rob, Very sorry!
I'll take a look and Mod. the Filter as you have directed.
B.
I'll take a look and Mod. the Filter as you have directed.
B.
I have just noticed a subtlety in the question you asked earlier:
As this column in the data contains numerous 3 letter combinations, based on the sample the value in that column cannot be QPM AND OAP, it can only be one or the other (or one of the other values).
Thanks
Rob H
don't you mean; "Heading4 equal to QPM AND OAP"?I mentioned the correction from Heading5 to Heading4 but just noticed as well your use of the word AND between the two values whereas I used OR.
As this column in the data contains numerous 3 letter combinations, based on the sample the value in that column cannot be QPM AND OAP, it can only be one or the other (or one of the other values).
Thanks
Rob H
Expanding on the earlier comment with various types of criteria, to get an AND match on one column you can use the same heading twice. The only use I can think of for this though would be to look at a range, eg a range of dates, where you want to return entries that are greater than one value AND less than another, for example:
Heading3 Heading3
"<="&31/12/2014 "=>"&31/01/2014
Would return entries where date under Heading3 is within 2014.
Thanks
Rob H
Heading3 Heading3
"<="&31/12/2014 "=>"&31/01/2014
Would return entries where date under Heading3 is within 2014.
Thanks
Rob H
ASKER
Rob,
What I have been trying to do is to filter a range (Originalrange) to find a match (ABC, XYZ, etc.), put them into a results table (Resultsrange) and then also add below the results, another filter that is on a list of other matches that do not include the original matches. I believe you have tried to give me a solution that does all this in one pass using Advanced Filter. The problem is, I cannot get it to work. I've attached the changes you told me to make but when I go to the Results Data Table and do the Advanced Filter, I don't get the results.
B.
Data-Extract-Using-Advanced-Filteringv4.
What I have been trying to do is to filter a range (Originalrange) to find a match (ABC, XYZ, etc.), put them into a results table (Resultsrange) and then also add below the results, another filter that is on a list of other matches that do not include the original matches. I believe you have tried to give me a solution that does all this in one pass using Advanced Filter. The problem is, I cannot get it to work. I've attached the changes you told me to make but when I go to the Results Data Table and do the Advanced Filter, I don't get the results.
B.
Data-Extract-Using-Advanced-Filteringv4.
ASKER
Phillip,
I continue to get a Debug error when I run the Macro. Can you take a quick look?
thank you,
B.
Data-Extract-Using-Filtering-Macro.xlsm
I continue to get a Debug error when I run the Macro. Can you take a quick look?
thank you,
B.
Data-Extract-Using-Filtering-Macro.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rob, Thank you! Got it to work. One last question, when I put in the selection criteria in Heading 4, can it look for the three letters in "a string" or is it simply looking for an exact match? For example, what if I have "QPM is in the field"; if QPM is the search criteria, will it recognize that record because QPM is in the sentence or string?
B.
B.
You can use wild cards
*QPM* will match strings containing QPM
*QPM* will match strings containing QPM
ASKER
Rob,
Thank you for all the help! You taught me something new as well as helped me out with my project.
Thank you again,
B.
Thank you for all the help! You taught me something new as well as helped me out with my project.
Thank you again,
B.
You can use the same criteria principles in the Dbase formulas, eg DSUM, DMAX, DMIN etc.
The Dbase functions are fine if you only want one result from a datatable, the formula can only refer to one set of criteria.
For numerical results, it is possible to get round it by extending the Criteria as you copy down your formula and then deducting previous results.
Thanks
Rob H
The Dbase functions are fine if you only want one result from a datatable, the formula can only refer to one set of criteria.
For numerical results, it is possible to get round it by extending the Criteria as you copy down your formula and then deducting previous results.
Thanks
Rob H
Incidentally, I have changed your criteria, as it would not have given the results you wanted.
EE150114.xlsm