#1
|
|||
|
|||
Pivot table v Advance filtering v Macro v VLookup
I have an extensive worksheet where I want to bring over specific data to a new worksheet. What I am trying to do is determine the best approach.
There are 18 columns with data, number of rows just depends. Out of the 18 columns, there are 3 columns that I use to determine as to what data needs to be brought over to the new worksheet. I need all columns to the left and right for each row that contains my specific parameters to be brought over, keeping totals as well. All data that does not meet the parameters in not necessary. On the attached document the RAW Data tab is what is brought over, the row/s columns are the parameters I use to filter. The Result tab is what I need for the final worksheet to look like. What would be the best approach - Pivot Table, Advanced Filter, Macro OR VLookup |
#2
|
|||
|
|||
I would prefer Advanced Filter and have tried to do just that in the attached in sheet RESULT cell U9:AL100 with Criteria range in U1:W5.
It seems to work reasonably well but there are some problems that I can't explain. As you can see I try to compare your results in A9:R101 with those generated by Advanced Filter in U9:AL100 in AN9:BE100. Please note that these formulas return 7 FALSE's in row 18 and row 92. Edit I now realize that the above problem is due to the fact that there are duplicates of the headings in row 8 in sheet RAW DATA (two instances of FY12, FY13 etc. I tried to change L8 to FY12_2, M8 to FY13_2 etc. and it worked fine). Last edited by xor; 04-15-2016 at 05:08 PM. |
#3
|
|||
|
|||
Perfect! I will take a look. Might need to ping you regarding the specifics of how you set up the advanced filter.
Appreciate your time. I will get back with you after reviewing. |
#4
|
|||
|
|||
This worked perfectly and seamlessly.
With this work around you have saved me many hours of searching and formatting. Thanks much!! |
Tags |
advance filtering, macro, pivot table |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create PIVOT chart using few rows in pivot table | Santhosh_84 | Excel | 1 | 08-31-2015 06:22 AM |
Filtering (advanced?) in Pivot Table | canajun | Excel | 6 | 01-17-2015 05:01 AM |
Excel Macro to "Select All" in Pivot Table Field | galkej | Excel | 0 | 02-03-2014 11:59 AM |
Help with pivot table macro | ejima | Excel Programming | 2 | 02-16-2012 03:45 PM |
VBA: Recording a macro to insert a PIVOT table | e093223 | Excel Programming | 0 | 10-09-2011 01:55 AM |