Vlookup with Conditions

diddyk

New Member
Joined
Aug 8, 2012
Messages
4
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:
CXXXX1CXXXX2CXXXX3CXXXX4
10014000
10021000070006540
100340009000
100480006000

<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

Well-known Member
Joined
Feb 2, 2012
Messages
4,014
Office Version
365
Platform
Windows, Mobile
Hi,
won't you update your pivot table or add fields or change fileds? this may throw off your table.
 

diddyk

New Member
Joined
Aug 8, 2012
Messages
4
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,014
Office Version
365
Platform
Windows, Mobile
Noted.
if pivot as follows:

CXXXX1CXXXX2CXXXX3CXXXX4
10014000
10021000070006540
100340009000
100480006000

<!--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

New Member
Joined
Aug 8, 2012
Messages
4
Noted.
if pivot as follows:

CXXXX1CXXXX2CXXXX3CXXXX4
10014000
10021000070006540
100340009000
100480006000

<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

Well-known Member
Joined
Feb 2, 2012
Messages
4,014
Office Version
365
Platform
Windows, Mobile
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,014
Office Version
365
Platform
Windows, Mobile
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:
GLCCvalue
1002cxxx110000
1004cxxx18000
1002cxxx27000
1003cxxx24000
1002cxxx36540
1004cxxx36000
1001cxxx44000
1003cxxx49000
1006cxxy11234
1007cxxy15678
1008cxxz29101

<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:
76
Unique GLUnique CC
1001cxxx1
1002cxxx2
1003cxxx3
1004cxxx4
1006cxxy1
1007cxxz2
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:
resultsCC
GLcxxx1cxxx2cxxx3cxxx4cxxy1cxxz2
10014000
10021000070006540
100340009000
100480006000
10061234
10075678
10089101

<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:
cxxx1cxxx2cxxx3cxxx4cxxy1cxxz2
1001000400000
10021000070006540000
1003040000900000
1004800006000000
1006000012340
1007000056780
1008000009101

<tbody>
</tbody>

Link to your file.

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

Forum statistics

Threads
1,085,718
Messages
5,385,423
Members
401,946
Latest member
Jimmerjammer

Some videos you may like

This Week's Hot Topics

Top