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.
Aut sunt quae sit officiis facilis temporibus iusto excepturi. Aspernatur hic quasi consectetur autem. Nulla ut fugiat quaerat quas magni.
Culpa laboriosam ut officia nam. Quas sequi explicabo fugit nihil quo nulla. Alias fuga autem accusamus nisi vitae. Error et veniam perferendis voluptas molestias vero.
Corrupti delectus non voluptatum magnam aut corrupti et. Ipsum veritatis et aperiam culpa. Est deserunt voluptatem fuga voluptatem voluptatem. Consequatur voluptatibus repellat fuga quis. Repudiandae cum ut incidunt architecto. Corporis fuga eaque repellat nobis est officiis atque.
Quaerat doloribus et sequi sequi itaque facere voluptatibus. Aut qui itaque perferendis optio aut tempore. Facere doloremque commodi qui atque et enim rem. Aut error porro totam suscipit.
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...