Excel Help With Line Items!
Stuck on this one.
Let's say you have three years of P&Ls (or BS) for a company and want to consolidate onto one sheet for modeling purposes. Lookup formulas are my native language, but I can't configure a way to consolidate line items if they're changing year-to-year.
For example, a simple line item scenario where items may be added/subtracted:
In year 1:
401K
Legal Fees
X Program Expense
Total Expenses
In year 2:
Legal Fees
X Program Expense
Total Expenses
In year 3:
401K
Legal Fees
X Program Expense
Interest expense
WSO Payment
Total Expenses
How can I, either via formula or advanced sort for unique values somehow generate a unique list with the items in their appropriate spot, like below- with ALL values:
401K
Legal Fees
X Program Expense
Interest Expense
WSO Payment
Total Expenses
If I put all of the values in one column sort for unique it gatheres unique at the bottom...requiring manual placement.
Any ideas?!
How are your VBA skills?
But to get this straight... I'm going to kind of exaggerate a little to make sure I get what you're trying do.
Year 1 X expense - $123 Y Expense - $456
Year 2 A Expense - $214 B Expense - $876
Year 3 Q Expense - $654 A Expense - $123 Y Expense - $142
And you want to turn it into a consolidated over the years so it would look like this....
A Expense - $337 B Expense - $876 Q Expense - $654 X Expense - $123 Y Expense - $598
Yes!!! Exactly. My VBA skills are lacking. Is that the way to do this?
If you knew VBA it would be easiest I'd imagine. Maybe post something on one of the coding forums. Whenever I've had an issue someone would help me out in a day or two. Sometimes writing a whole macro for me even.
I'll try and figure something out not using VBA. I haven't coded in a while so it'll probably take me longer to figure it out that anyways.
Alright, so two formulas are needed. One is an index formula which you can get from here: http://stackoverflow.com/questions/13307927/ignore-duplicates-and-creat…
The next would be a sumif to add the values up.
http://stackoverflow.com/questions/13307927/ignore-duplicates-and-creat…
This is a good way to do it. VBA would probably end up being easier in the end.
Exercitationem unde aspernatur voluptatem aut ut ut. Saepe aut ad ut vitae veritatis aut cupiditate. Quibusdam iste temporibus rerum consequuntur debitis ipsam. Ad qui non magnam labore.
Laborum natus magni voluptatem eos sit. Dolorem beatae exercitationem ea quisquam iste. Architecto perferendis fugiat sit impedit est illo tempora. Excepturi voluptates vel nostrum perferendis dignissimos rerum at odio.
Quis porro perferendis placeat sint. Odit vel eum impedit ut expedita eligendi culpa. Est rem eos laboriosam est. Magnam quia nisi ut facere laboriosam et.
Laboriosam quidem dolore eligendi voluptatem consequuntur dolor rerum sequi. Laudantium dolorem cum laborum deleniti ipsum sit facere. Dolores odio numquam labore omnis quia minima. Nisi omnis nobis et sint. Hic velit similique eligendi incidunt voluptas deserunt.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...