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:

	CXXXX1	CXXXX2	CXXXX3	CXXXX4
1001				4000
1002	10000	7000	6540
1003		4000		9000
1004	8000		6000

<tbody>
</tbody>

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:

	CXXXX1	CXXXX2	CXXXX3	CXXXX4
1001				4000
1002	10000	7000	6540
1003		4000		9000
1004	8000		6000

<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

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

 

[diddyk]

Noted.
if pivot as follows:

	CXXXX1	CXXXX2	CXXXX3	CXXXX4
1001				4000
1002	10000	7000	6540
1003		4000		9000
1004	8000		6000

<tbody>
</tbody>

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:

GL	CC	value
1002	cxxx1	10000
1004	cxxx1	8000
1002	cxxx2	7000
1003	cxxx2	4000
1002	cxxx3	6540
1004	cxxx3	6000
1001	cxxx4	4000
1003	cxxx4	9000
1006	cxxy1	1234
1007	cxxy1	5678
1008	cxxz2	9101

<tbody>
</tbody>

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:

7	6
Unique GL	Unique CC
1001	cxxx1
1002	cxxx2
1003	cxxx3
1004	cxxx4
1006	cxxy1
1007	cxxz2
1008

<tbody>
</tbody>

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:

results	CC
GL	cxxx1	cxxx2	cxxx3	cxxx4	cxxy1	cxxz2
1001				4000
1002	10000	7000	6540
1003		4000		9000
1004	8000		6000
1006					1234
1007					5678
1008						9101

<tbody>
</tbody>

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:

	cxxx1	cxxx2	cxxx3	cxxx4	cxxy1	cxxz2
1001	0	0	0	4000	0	0
1002	10000	7000	6540	0	0	0
1003	0	4000	0	9000	0	0
1004	8000	0	6000	0	0	0
1006	0	0	0	0	1234	0
1007	0	0	0	0	5678	0
1008	0	0	0	0	0	9101

<tbody>
</tbody>

Link to your file.

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