Solved

2 Pass Data Extract Macro Request

Posted on 2015-01-13
23
58 Views
Last Modified: 2015-01-16
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
0
Comment
Question by:Bright01
  • 12
  • 8
  • 3
23 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40548479
Please see attached.

Incidentally, I have changed your criteria, as it would not have given the results you wanted.
EE150114.xlsm
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40548600
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(Data!A:A),12)

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
0
 

Author Comment

by:Bright01
ID: 40548630
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.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40548752
> I believe that a macro that defines an initial range (Originaldata)

Select your table (cells A5:E17) before running the macro.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40548759
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:

Adv Filter screen shot
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
0
 

Author Comment

by:Bright01
ID: 40549314
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.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40549828
The macro is provided for you to adjust as you wish,
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40549976
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40549982
And yes I did mean heading4, must have been a typo.
0
 

Author Comment

by:Bright01
ID: 40550051
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40550733
Who's Ron? Last couple of posts you have addressed to Ron; assuming they are aimed at me.

Rob
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:Rob Henson
ID: 40550922
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40550927
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,COUNTIF(Original!$T$1:$T$20,">"&0),5)

This will give a range that is 5 columns wide and as many rows deep as required.

Thanks
Rob H
0
 

Author Comment

by:Bright01
ID: 40550988
Rob,  Very sorry!  

I'll take a look and Mod. the Filter as you have directed.

B.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40551002
I have just noticed a subtlety in the question you asked earlier:

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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40551017
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
0
 

Author Comment

by:Bright01
ID: 40551045
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.
0
 

Author Comment

by:Bright01
ID: 40551058
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
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40551075
Your Dynamic range for the Data source has changed, it has gone to the same as the Criteria range.

OriginalData should be defined as:

=OFFSET(Original!$A$1,0,0,COUNTA(Original!$A:$A),5)

Thanks
Rob H
0
 

Author Comment

by:Bright01
ID: 40551590
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40552337
You can use wild cards

*QPM* will match strings containing QPM
0
 

Author Closing Comment

by:Bright01
ID: 40553491
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40553507
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
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

760 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

19 Experts available now in Live!

Get 1:1 Help Now