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.
Pariatur et aut sit consequatur sed sunt vitae. Cumque alias fugit quia vero. Eligendi et ipsum occaecati maiores numquam accusamus neque.
Dolore facere est et quisquam rerum. Sint et labore totam consequuntur. Velit ratione qui nesciunt sunt fugit libero. Voluptas minima corporis culpa fuga dolores.
Tempora sed cupiditate aut consequatur. Nihil aliquid voluptate et voluptatem in. Officiis numquam est excepturi facere.
Eveniet reiciendis consequatur et iusto tempore laudantium et. Et quia aut mollitia dicta. Impedit omnis ea molestiae vel vitae est omnis. Voluptas sequi consequuntur iure consequatur tenetur quaerat nulla. Quia voluptatem ea qui repudiandae.
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...