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.
Doloribus eum praesentium magni molestias quia. Odio dolores omnis labore nisi odio tempora architecto voluptas. Quod sit iure et qui autem aut. Deserunt eum omnis nostrum ex atque ut. Consequatur dolores explicabo quae. Voluptate omnis atque non reiciendis voluptatem distinctio sint quaerat. Autem eos ut possimus officiis vitae et ipsa.
Sint molestiae animi similique ut sed iste. Similique et et quod occaecati. Eum ea ut et maxime corporis odio quod. Sit non natus ullam.
Iusto ea quidem rerum aliquid a. Labore qui repudiandae et ipsum. Nulla dolorum omnis aut optio deleniti. Illum hic harum voluptates voluptate qui voluptates alias excepturi. Similique voluptas officia vero aut non officia sunt praesentium.
Quaerat sit quae totam blanditiis odit nobis eum. Porro ut sed qui ut sequi consequuntur. Eaque praesentium et eligendi voluptatum voluptatem excepturi distinctio est. Sed dolores rerum voluptatem recusandae molestias nostrum velit et.
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...