VBA Vlookup & Pivot Table (Get Sum)

[unknownymous]

Hi Team,

I have this data with 100K lines and badly need to get a macro as I do this over and over again.

This is the scenario:

"Master" tab - Main Data Sheet
"Reference" tab - Country reference sheet

1.) In the "Master" tab, what I need to do is add a blank column where the macro looks up the COUNTRY and get its equivalent GROUP on "Reference" tab (col B).
The headers can be found on A2-AN2. Country tab can be found in column AE.


This is what Reference Sheet looks like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]United Kingdom[/TD]
[TD]UK & Ireland[/TD]
[/TR]
[TR]
[TD]Ireland[/TD]
[TD]UK & Ireland[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]North America[/TD]
[/TR]
[TR]
[TD]Norway[/TD]
[TD]ROE[/TD]
[/TR]
[TR]
[TD]Chile[/TD]
[TD]ROW[/TD]
[/TR]
</tbody>[/TABLE]


2.) Once the Country Group is ready, I need to pivot that section together with Columns F2:I2 and K2:W2 (where I need to get the sum up to the last non-blank column and the number format is xx,xxx). Next, rename the pivot tab as "PivotedData".


Any help will be much appreciated.

 

[haadiabdul504]

Replace the entire GETPIVOTDATA function and arguments with the cell reference to the cell concerned, and then copy down. e.g. <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">=VLOOKUP(E2,SSSContribution[#All],3,TRUE) </code>Note that you can turn off the automatic generation of the GETPIVOTDATA syntax by unchecking this option:

Facebook Marketing