• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index/Match and Pivot Tables

I

IAmLemondrop

New Member
Hello. I’m trying to display some budget data and utilize some of Excel’s calculated fields, but I can’t get it to work properly.


I’m using an Index/Match formula to combine this data:

[pre]
Code: 
Item	Budget
123	 10,000
124	 20,000
125	 15,000
126	 30,000
And this data:

[pre][code]Item	Cost Accounts
123	987
123	988
123	999
124	568
124	456
124	123
125	789
125	458
126	890
126	258
[/pre]
Against these actuals in order to create a pivot table.


Budget Item Cost Account Actuals
10,000 123 987 235
10,000 123 988 123
10,000 123 999 55.68
20,000 124 568 2.88
20,000 124 456 1.23
20,000 124 123 2.05
15,000 125 789 2.58
15,000 125 458 9.08
30,000 126 890 10.01
30,000 126 258 5.68[/code][/pre]
Now my problem is because each item is displayed more than once, it brings in the budget more than once. So using the example above, Item #123 would show a budget of $30,000 which isn’t right. Any suggestions? I’ve tried the multiple consolidation ranges, but it doesn’t give me the flexibility to put in calculated fields or really move around my data. (In reality there are more columns than cost account and actuals, but the budget field is the only one giving me problems).
 
Hi, IAmLemondrop!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this particular question... would you please indicate which is the desired output?


Regards!
 
Thank you for the response. I've been searching here and elsewhere for two days now and maybe I'm just not framing my question properly, because I've not found anything resembling my problem.


Ideally, I'd like to put that last little table above into a pivot table. However when I do that the budget amounts (first column), total together. So Item #123 shows a budget of $30,000 when it's only 10,000. The multiple consolidation ranges don't allow me to fully utilize my data. I only get the fields "Row/Column/Value/Page 1" when I need the users to be able to select if they want to show the cost account, actuals, etc.


I have a sample spreadsheet with the data I showed above, but I can't log into any of those file sharing sites from work.
 
Hi, IAmLemondrop!

I was referring to post the output that you want to get, as you posted the tables in your first post.

Regards!
 
I'm trying to get this:

[pre]
Code: 
Item	Sum of Budget	Sum of Actuals	Sum of (Over) / Under Budget
123	 30,000 	413.68	         29,586
124	 60,000 	6.16	         59,994
125	 30,000 	11.66	         29,988
126	 60,000 	15.69	         59,984
Grand Total  180,000 	447.19	         179,553
[/pre]
But I want the budget to say $10K instead of $30K
 
Hi, IAmLemondrop!


I'd begin changing the titles of 2nd and 4th column: Sum of Budget?, just Budget; Sum of (Over..., just Over...


If this is the table you want to achieve:

-----

[pre]
Code: 
Item	Budget	Sum of Actuals	Over / Under Budget
123	10000	413,68		9586,32
124	20000	6,16		19993,84
125	15000	11,66		14988,34
126	30000	15,69		29984,31
[/pre]
-----


Assuming your data starts at cell A1, place this formulas and copy down as needed:

B2: =BUSCARV(A2;A$25:B$28;2;FALSO) -----> in english: =VLOOKUP(A2,A$25:B$28,2,FALSO)

C2: =SUMAR.SI(K$25:K$34;A2;M$25:M$34) -----> in english: =SUMIF(K$25:K$34,A2,M$25:M$34)

D2: =B2-C2

where A$25:B$28 is your 1st table range and J$25:M$34 is your 3rd table range.


Regards!
 
Well those columns have sum of budget, sum of actuals, sum of over/under because they are supposed to be in a pivot table.

[pre]
Code: 
Item	Budget	Sum of Actuals	Sum of Over/Under Budget	What sum of Over/Under should be displaying
123	 10,000 	413.68	 29,586 	                 9,586
124	 20,000 	6.16	 59,994 	                19,586
125	 15,000 	11.66	 29,988 	                14,586
126	 30,000 	15.69	 59,984 	                29,586
Grand Total		447.19	 179,553 	                73,345
[/pre]
I can work around the budget displaying properly if I put it in a row instead of with the values, but when I try to calculate anything off of it, it doesn't calculate correctly.
 
Hi, IAmLemondrop!

Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!
 
Finally got the sample file to upload from my phone:

https://www.dropbox.com/s/ovlvqo46nihgzlh/PivotTable%20Lookup.xlsx
 
So I made a few more adjustments to it. I used an if & index/match to pull in the budget data only at the first instance of each item. This is basically how I want it to look. However the budget is really tied to the item not the cost account. Any way to remove the details on the Sum Over/Under column and just leave the sum?


https://www.dropbox.com/s/4onla9ry4p6xh0c/PivotTable%20Lookup2.xlsx
 
Hi, IAmLemondrop!


Give a look at this file, based on 1st uploaded workbook:

https://dl.dropboxusercontent.com/u/60558749/Index_Match%20and%20Pivot%20Tables%20-%20PivotTable%20Lookup%20%28for%20IAmLemondrop%20at%20chandoo.org%29.xlsx


It's not a natural but a tricky way to achieve it, but your PT isn't a normal PT, so the alternatives are splitting the PTs in two or adding a column to the source table.


Regards!
 
Sorry for the delay in getting back to you. I can't open dropbox from my phone (or work), but your fix works fine. I never would have thought of it. Thank you!
 
Hi ,


An alternative method , again referring to your original upload :


In cell L3 , enter the following formula , and copy down :


=F3-GETPIVOTDATA("Actuals",$F$1,"Budget",10000)


Narayan
 
You must log in or register to reply here.
Back
Top