Vlookup with Conditions

[diddyk]

Hi, I'm hoping for some help writing a formula. I have a pivot table which is an extract of accounting data. I need to move the data from the pivot table to a template used in a monthly report.

Essentially, my pivot table is Cost centre numbers as the Column labels, and General Ledger numbers for the row labels. The problem is there are hundreds of cost centres and thousands of GL codes. In my template, I want to use a Vlookup to return the GL value, but for each individual cost centres. I'm hoping there is an easier way then setting up a Vlookup for each cost centre to return the value in a certain column number, as the pivot table I'm using changes from month to month, Some months certain cost centres are added or removed.)

Here is a sample of what my pivot table looks like:
[TABLE="class: outer_border, width: 500, align: right"]
<tbody>[TR]
[TD][/TD]
[TD]CXXXX1[/TD]
[TD]CXXXX2[/TD]
[TD]CXXXX3[/TD]
[TD]CXXXX4[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]10000[/TD]
[TD]7000[/TD]
[TD]6540[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD][/TD]
[TD]4000[/TD]
[TD][/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD]6000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My template looks similar, however it is just a data table. I need to move the summarized data from the pivot table to the template to feed other reports.

Can I use a vlookup on the condition that the template column label matches the pivot table column label? I hope this is clear enough.

Thanks

 

[cyrilbrd]

Hi,
won't you update your pivot table or add fields or change fileds? this may throw off your table.

 

[diddyk]

Hi,
won't you update your pivot table or add fields or change fileds? this may throw off your table.

The pivot table will always have the same layout, cost centres across the top and GL accounts for the rows. The only information that will change is that values within the table. I need a formula to retrieve that data for my template. Perhaps using an Hlookup for the column and a vlookup to return the value for the GL? I know I can use a vlookup to return the value for the GL, however I don't want to write out hundreds of vlookups for each column value, especially when the column number may be different from month to month.

 

[cyrilbrd]

Noted.
if pivot as follows:

[TABLE="width: 325"]
<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"][/TD]
[TD="class: xl63, width: 65"]CXXXX1[/TD]
[TD="class: xl63, width: 65"]CXXXX2[/TD]
[TD="class: xl63, width: 65"]CXXXX3[/TD]
[TD="class: xl63, width: 65"]CXXXX4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1001[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]4000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1002[/TD]
[TD="class: xl63, align: right"]10000[/TD]
[TD="class: xl63, align: right"]7000[/TD]
[TD="class: xl63, align: right"]6540[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1003[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]4000[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]9000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1004[/TD]
[TD="class: xl63, align: right"]8000[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]6000[/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

How would you like the "table" built from the pivot to look like?

 

[diddyk]

Noted.
if pivot as follows:

[TABLE="width: 325"]
<tbody>[TR]
[TD="width: 65"][/TD]
[TD="class: xl63, width: 65"]CXXXX1[/TD]
[TD="class: xl63, width: 65"]CXXXX2[/TD]
[TD="class: xl63, width: 65"]CXXXX3[/TD]
[TD="class: xl63, width: 65"]CXXXX4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1001[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]4000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1002[/TD]
[TD="class: xl63, align: right"]10000[/TD]
[TD="class: xl63, align: right"]7000[/TD]
[TD="class: xl63, align: right"]6540[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1003[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]4000[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]9000[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1004[/TD]
[TD="class: xl63, align: right"]8000[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]6000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How would you like the "table" built from the pivot to look like?

It essentially is the same, cost centres across the top and GL's down the side. Occasionally a new GL or Cost centre is added into the mix and the template needs to be adjusted. I would straight copy and paste, however due to changes in coding, sometimes it doesn't line up exactly. I am looking for a formula that will Vlookup the value in the GL for the data table, but only in the column for which the cost centre number in the pivot table is the same.

 

[cyrilbrd]

Ok given that GL (1001,1002,1003,1004...) and Cost Center (alias CC) (CXXX1, CXXX2, CXXX3, CXXX4...) may be added in the raw data, then the pivot will definitely change, that is understood.
If you have a list of all possible GL and CC, we could come up with a solution, if such list doesn't exist because new CC or GL may be added / created in a later time, we could then create a unique list that would sort itself, an INDEX MATCH would then fetch the data, although I wonder how it would go with the pivot's range...

What do you think, would this work?

 

[cyrilbrd]

I propose the following:
4 tabs:
1-one database (DB) that would sort the unique data from your raw data.
2-one Raw data (RD) containing the data you wish to study.
3-one pivot table (PT)
4-one table that would extract the data from the pivot table (recap)

RD may look as follows:
[TABLE="width: 260"]
<tbody>[TR]
[TD]GL[/TD]
[TD]CC[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]cxxx1[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]cxxx1[/TD]
[TD]8000[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]cxxx2[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]cxxx2[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]cxxx3[/TD]
[TD]6540[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]cxxx3[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]cxxx4[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]cxxx4[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]1006[/TD]
[TD]cxxy1[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD]cxxy1[/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]cxxz2[/TD]
[TD]9101[/TD]
[/TR]
</tbody>[/TABLE]

Four Named Ranges are created here:
ListGL would be =OFFSET(RD!$A$2,0,0,COUNT(IF(RD!$A$2:$A$1000="","",1)),1)
ListCC would be =OFFSET(RD!$B$2,0,0,COUNT(IF(RD!$B$2:$B$1000="","",1)),1)
RvCC would be =ROW(ListCC)-ROW(RD!$B$2)+1
RvGL would be =ROW(ListGL)-ROW(RD!$A$2)+1

As for the data kindly just make sure to sort them per Column B (CC).

DB will look like this:
[TABLE="width: 173"]
<tbody>[TR]
[TD]7[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Unique GL[/TD]
[TD]Unique CC[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]cxxx1[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]cxxx2[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]cxxx3[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]cxxx4[/TD]
[/TR]
[TR]
[TD]1006[/TD]
[TD]cxxy1[/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD]cxxz2[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Add one Named Range here called UniqueCC =DB!$B$3:$B$300

In A1 type: =SUM(IF(FREQUENCY(IF(ListGL<>"",MATCH("~"&ListGL,ListGL&"",0)),RvGL),1))
Ctrl + Shift + Enter, not just enter.
That would give you in this example a 7 as there are 7 different GL codes (1001,1002,1003,1004,1006,1007 and 1008)

In B1 Type:
=SUM(IF(FREQUENCY(IF(ListCC<>"",MATCH("~"&ListCC,ListCC&"",0)),RvCC),1))
Ctrl + Shift + Enter, not just enter.
That would give you in this example a 6 as there are 6 different CC codes (cxxx1,cxxx2,cxxx3,cxxx4,cxxy1 and cxxz2)
In A3 type:
=IFERROR(INDEX(ListGL,MATCH(0,IF(MAX(NOT(COUNTIF(RD!$F$1:F1,ListGL))*(COUNTIF(ListGL,">"&ListGL)+1))=(COUNTIF(ListGL,">"&ListGL)+1),0,1),0)),"")
Ctrl + Shift + Enter, not just enter.

In B3 type:
=IFERROR(INDEX(ListCC,MATCH(0,IF(MAX(NOT(COUNTIF(RD!$G$1:G1,ListCC))*(COUNTIF(ListCC,">"&ListCC)+1))=(COUNTIF(ListCC,">"&ListCC)+1),0,1),0)),"")
Ctrl + Shift + Enter, not just enter.

Copy down till needed.


PT may look as follows:
[TABLE="width: 331"]
<tbody>[TR]
[TD]results[/TD]
[TD]CC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GL[/TD]
[TD]cxxx1[/TD]
[TD]cxxx2[/TD]
[TD]cxxx3[/TD]
[TD]cxxx4[/TD]
[TD]cxxy1[/TD]
[TD]cxxz2[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]10000[/TD]
[TD]7000[/TD]
[TD]6540[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD][/TD]
[TD]4000[/TD]
[TD][/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD]6000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1234[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5678[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9101[/TD]
[/TR]
</tbody>[/TABLE]

Make sure to rename the Sum of values, the Column and Rows Labels as shown above that is “results”, “CC” and “GL”. Here the pivot is in A3.

You may choose a range bigger than your actual data range in RD, such to ensure that whatever data added may be automatically added upon refreshing the PT.

Recap would be as follows:
In A3 type: =TRANSPOSE(DB!$A$3:$A$300)
Copy down till row 300.
You may opt to increase the range of this if you have thousand of GL code by modifying the range from 300 to 3000…

In B2 type: =INDEX(UniqueCC,COLUMN()-COLUMN($B$2)+1)
Ctrl + Shift + Enter, not just enter.
Copy to the right till needed.
In B3 type:
=IF(ISBLANK((GETPIVOTDATA("results",PT!$A$3,"CC",B$2,"GL",$A3))),"",IFERROR(GETPIVOTDATA("results",PT!$A$3,"CC",B$2,"GL",$A3),""))
Enter.
Copied right and down till needed.

The result would be something like this:
[TABLE="width: 607"]
<tbody>[TR]
[TD][/TD]
[TD]cxxx1[/TD]
[TD]cxxx2[/TD]
[TD]cxxx3[/TD]
[TD]cxxx4[/TD]
[TD]cxxy1[/TD]
[TD]cxxz2[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]10000[/TD]
[TD]7000[/TD]
[TD]6540[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]0[/TD]
[TD]4000[/TD]
[TD]0[/TD]
[TD]9000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]8000[/TD]
[TD]0[/TD]
[TD]6000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1006[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1234[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5678[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1008[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]9101[/TD]
[/TR]
</tbody>[/TABLE]

Link to your file.

The idea was to make the recap as dynamic as possible.
Is this close to what you needed?