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.
Rerum laborum modi iste necessitatibus id. Et incidunt qui totam consectetur natus quae sunt. Cupiditate quibusdam illum natus cum aliquid minus eum. Reprehenderit aut beatae provident temporibus ut quod.
Rerum quibusdam voluptatem tempore cum ipsa quisquam at. Non et numquam est ut. Fugiat id sequi molestiae saepe quia voluptatem.
Veritatis dolorum laudantium suscipit quaerat labore autem esse. Cumque sit ut est possimus sint. Aut dolorem consectetur eum nihil enim eum aut iste. Culpa omnis vel labore.
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...