VBA vlookup to Reference Sheet & Create a Pivot

[unknownymous]

Hi Guys,

I have a massive data set that needs vlook up and pivot. I have this excel file named "MasterData" with two tabs sheets namely: Master & Reference. The master contains a list of data while reference sheet has list of countries and its equivalent country group.

Example Reference Sheet:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]North America[/TD]
[/TR]
[TR]
[TD]Sweden[/TD]
[TD]Sweden[/TD]
[/TR]
[TR]
[TD]South Korea[/TD]
[TD]Rest of World[/TD]
[/TR]
[TR]
[TD]United Kingdom[/TD]
[TD]Rest of Europe[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]Norway[/TD]
[TD]Norway[/TD]
[/TR]
</tbody>[/TABLE]

What the macro does:
*In Master Sheet (Column Header can be found on Row A4:Z4)
1) Need to find the "Country" column header
2) Add a blank column next to it
3) vlook up "Country" column (until the last blank cell) against Reference tab and get its equivalent country "Group"
4) Once we have the Country Group, we need to pivot it together with Columnn with header name "Total", "Year 1", "Year 2" & "Year 3". The pivoted tab will be renamed as "PivotData"


I'm having a hard time looking for codes and took me along time working on this data so any help will be much appreciated.

Thanks!

 

[Saba Sabaratnam]

Hi

I have written codes to solve your problem and assume the following.

You have three sheets "Master", "Reference" and "PivotData"

You have three fields "Year 1", "Year 2" and "Year 3". if not, you change it in the following section of the code.


With PVT

.AddDataField PVT.PivotFields("Year 1"), Caption:="Total: Year 1", Function:=xlSum
.AddDataField PVT.PivotFields("Year 2"), Caption:="Total: Year 2", Function:=xlSum
.AddDataField PVT.PivotFields("Year 3"), Caption:="Total: Year 3", Function:=xlSum


End With

Create a module and copy the following code.

sub Pivot_Table()

Dim M As Worksheet
Dim No_Col As Long
Dim No_Row As Long
Dim PC As PivotCache
Dim PVT As PivotTable

Set M = ActiveWorkbook.Sheets("Master")

No_Col = M.Range("A1").CurrentRegion.Columns.Count
No_Row = M.Range("A1").CurrentRegion.Rows.Count

M.Activate
ActiveSheet.Range("a4").Activate


Rem delete any column with heading 'Group' to avoid dulplicate columns

For col = 1 To No_Col

If ActiveCell.Value = "Group" Then

ActiveCell.EntireColumn.Delete

Else

ActiveCell.Offset(0, 1).Activate


End If


Next col


Rem create a column headed "Country"

M.Range("a4").Activate


For col = 1 To No_Col

If ActiveCell.Value = "Country" Then

ActiveCell.Offset(0, 1).EntireColumn.Insert
ActiveCell.Offset(0, 1).Value = "Group"
ActiveCell.Offset(1, 1).Activate

For Row = 2 To No_Row

ActiveCell.Formula = "=vlookup(Master!RC[-1], reference!C1:C2,2,false)"
ActiveCell.Offset(1, 0).Activate

Next Row

Exit For


Else

ActiveCell.Offset(0, 1).Activate


End If



Next col


Rem create a pivot table

Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveWorkbook.Sheets("Master").Range("A4").CurrentRegion.Address)


ActiveWorkbook.Sheets("PivotData").Cells.Clear


Set PVT = PC.CreatePivotTable(tabledestination:=ActiveWorkbook.Sheets("PivotData").Range("A5"), TableName:="PT")



With PVT

.PivotFields("Group").Orientation = xlRowField



End With


With PVT

.AddDataField PVT.PivotFields("Year 1"), Caption:="Total: Year 1", Function:=xlSum
.AddDataField PVT.PivotFields("Year 2"), Caption:="Total: Year 2", Function:=xlSum
.AddDataField PVT.PivotFields("Year 3"), Caption:="Total: Year 3", Function:=xlSum




End With




End Sub



Kind regards

Saba

 

[Saba Sabaratnam]

My other assumption is that you have the following data in column A:B in "Reference" sheet;

[TABLE="width: 165"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]North America[/TD]
[/TR]
[TR]
[TD]Sweden[/TD]
[TD]Sweden[/TD]
[/TR]
[TR]
[TD]South Korea[/TD]
[TD]Rest of World[/TD]
[/TR]
[TR]
[TD]United Kingdom[/TD]
[TD]Rest of Europe[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]Norway[/TD]
[TD]Norway[/TD]
[/TR]
</tbody>[/TABLE]

 

[unknownymous]

My other assumption is that you have the following data in column A:B in "Reference" sheet;

[TABLE="width: 165"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]North America[/TD]
[/TR]
[TR]
[TD]Sweden[/TD]
[TD]Sweden[/TD]
[/TR]
[TR]
[TD]South Korea[/TD]
[TD]Rest of World[/TD]
[/TR]
[TR]
[TD]United Kingdom[/TD]
[TD]Rest of Europe[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]Norway[/TD]
[TD]Norway[/TD]
[/TR]
</tbody>[/TABLE]

This is correct Saba - there's another tab called "Reference".


By the way, can we add a code for finding the "Country" column as it is interchangeable in the "Master" tab.

I'm having error (run time error "9") in this part:

ActiveWorkbook.Sheets("PivotData").Cells.Clear


Appreciate your help.