VLOOKUP instead of GETPIVOT

[scottleger4]

Good morning,

I have a rather large data source file with a large pivot table. In another file, I need to pull several pieces of information in order for a macro to work. The problem I am having is that the volume of GETPIVOT formulas I currently have are really bogging everything down. The solution I have in mind is to "transfer" the appropraite cut of the data I need into my second file and do VLOOKUPs instead.

I'm running into a problem though. I'm trying to get to a sum of the number of units in an area, but the data behind the pivot has the number of units further broken down to individual locations, which I don't need and would render my VLOOKUP useless as it would only find whatever the first entry in the list is. If my Pivot Table looks like...

Country
....Region
......Area
..........Unit 1 - 6
..........Unit 2 - 10
..........Unit 3 - 21

...is there a way to get this in a list so that I have individual columns...

Country Region Area Unit 1 6
Country Region Area Unit 2 10
Country Region Area Unit 3 21

...from this I could create a unique identifier and do what I need to do. I thought if I double clicked the Totals at the bottom of the Pivot Table it would do what I wanted, except it threw in all the other data fields too. For example...

Country Region Area District Branch 1 Unit 1 2
Country Region Area District Branch 2 Unit 1 1
Country Region Area District Branch 3 Unit 1 1
Country Region Area District Branch 4 Unit 1 2

Would appreciate any ideas you have. Thanks in advance.

 

[=ODIN=]

Sorry, I might be confused here, but if your version of Excel is 2010+ I think you can achieve what you're looking for by changing a few settings on your pivot.


Select pivot table, then click on Ribon:
Design --> Report Lay Out --> Show in tabular format
Design --> Report Lay Out --> Repeat All item labels
Design --> Subtotals --> Do not show subtotals

 

[scottleger4]

It wasn't exactly what I was asking, but the end result of what you suggested is actually going to work a LOT better. Thanks SO MUCH! This board always comes through for me!