Create consolidated income statement from separate statements

[max3732]

Combine names example for excel forum.xlsm

What I'm trying to do is combine separate income statements into one consolidated statement. All of the statements are basically done the same way, but each statement has certain categories that the others don't have. For example, property 1 may have "late fees" and property 2 may have "snow removal expense" that doesn't appear in the other statements.

The example I gave is a simplified revenue example, but I'm trying to do it for the whole statement. So there are reimburseable expenses, non reimbursable expenses, and various incomes before ending at "NET INCOME" for all the statements.

You can see my attempt at it here, where it was suggested I post the problem to the forum:
http://www.excelforum.com/excel-prog...n-a-range.html

Just getting the data to the point you see in the example sheet took a lot of work. Thanks in advance for your help.

[humdingaling]

Hi

will this not work?

CSE formula

Please Login or Register  to view this content.

put into column I

[jindon]

vba solution

[max3732]

2nd Example for Excel Forum.xlsx

I couldn't get the other solutions to work. Here are 3 of the income statements I'm looking at. I just put garbage numbers in them just to show there are numbers next to the names

[humdingaling]

unsure whos solution you are referring to

if you wanting to use formula method you have to add headings in like you did in the first example in order for it to work and you will need to make the array field larger to cater for the extra fields

it is a CSE (CTRL+SHIFT+ENTER) formula meaning you have to press f2 and then those 3 buttons in order for the formula to work
if it is successful you should see {} around the formula

tested jindon's code
its quick and works great plus makes headers in for you


attached both in the same sheet

[jindon]

it is a CSE (CTRL+SHIFT+ENTER) formula meaning you have to press f2 and then those 3 buttons in order for the formula to work
if it is successful you should see {} around the formula

No need to be CSE, if you change it to

=SUMIF($A$3:$E$24,G4,$B$3:$F$24)

[humdingaling]

Thanks Jindon
did not know you can offset the array like that in sumif

[max3732]

The problem with the code is that it doesn't put the income statements items in the correct order. "Total Net Income" should be the last item. The code needs to preserve the order as each sub category leads to totals. You can't just put all the items that didn't appear in all 3 at the end. The example, "Total General Repairs and Maint." is made up of "Hvac", "HVAC-R&M", and "Hvac-Supplies". The code puts "Hvac-Supplies" after Net Income. You have Revenue items, expense items, and things like depreciation all with subcategories like "recoverable" and "nonrecoverable". So all the subcategories need to match up and to get to Net Income you subtract the sum of all the Revenue and Expense Subcategories and subtract them to get "Total Net Income". This data is exported from a PDF so you can't see it here.

So what I'm saying is that order matters and it needs to combine it in a way that preserves each subcategory and makes sense.

I wrote code that accomplishes this with the categories, but it runs very slowly with multiple loops that I'm sure is very inefficient. I'll post it in the morning

[humdingaling]

hmmmm couldn't you just have a full list of categories with all subcategories in order, use the sumif formula and filter out things with zero before you export for pdf ?

[jindon]

Then you must list all the sub categories in order somewhere.

[max3732]

The data was taken from a PDF and I wrote a lot of code to get it into this format. You can see the most of the categories in caps, although some have more categories than others.

I've attached a sheet with what I did with only 2 columns, with the idea that after I finished column B I could move what was in column C into it and repeat the process. I also have to figure out how to add up the numbers and put them next to the respective categories.

My code works perfectly as far as sorting the categories and keeping the order intact. The problem with my code is that it's extremely slow. I tried to document what I'm doing. It would be a huge help if you could figure out a way to accomplish the same thing more efficiently.

Please Login or Register  to view this content.

[jindon]

This is working for the sample data you posted in #1.

All I need is a full categories and sub categories in order, so that you don't need to sort.

Please Login or Register  to view this content.

[max3732]

I'm not sure how to provide the categories. I've attached a worksheet where I organized the same the categories from example 2 for 1 property and actually added the values so you can see how they relate. As far I know this includes all the categories (things in all caps that start with "TOTAL").

As you can see from the example provided, the values in each subcategory are in alphabetical order. If there is a new revenue/expense/depreciation item or one that's in one property, but not the other it needs to put it in the correct place when consolidating. For example, "Hvac-Supplies" appears in the 2nd property I attached in the 2nd example, so that needs to go after "HVAC-R&M" and before "Locks & Keys".

Please let me know if this is the information you were looking for to be able to help with this. Thanks!

[jindon]

OK, Let's go back to the file in your post #11.

If the procedure generate the combined list as you want, does it help to your main problem?

My result will appear D3 downward.

[max3732]

Yes, that's exactly what I was looking for and runs about an hour faster than my code. Now to finish the income statement problem I just need it to not only combine the categories, but also add the numbers. In a previous step I had put them all into an array and was going to use "if statements" and loops to add up the values for the combined statement, but is there a way you could do exactly what you did here and add up the values for all 3 properties like the "example 2" file?

If so I would be extremely appreciative

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[max3732]

Yes, it works great except for 1 small issue I didn't realize. There are some sub categories that appear both in "Recoverable Expenses" and in "Non-Recoverable Expenses".

Is there any way to differentiate them using your method? For example, right now its adding the HVAC value from the "Non-Recoverable Expenses" to the HVAC from the "Recoverable Expenses".

Other than that it seems to be working perfectly

[jindon]

Is that a irregular case?
If so, are there other cases?

You will need to make a list for them somewhere like

Category	Sub Category
Non-Recoverable Expenses	Hvac

[max3732]

I checked and some of these appear every month. There may be a new one added in the future, so is there any way it can identify if it's the 1st or 2nd occurrence within that property? Any duplicated items will appear after NON-RECOVERABLE EXPENSES.

I've attached a worksheet with the duplicates that you asked for.

[jindon]

The code will not extract duplicates of any main, sub, sub-sub categories.

If you want to identify the duplicate items to each specific categories separately, it will be totally different problem and it seems not possible under the structure of the data.

[max3732]

What about inserting a new, blank row before NON-RECOVERABLE EXPENSES and then running the code for the data before the blank row and again for the NON-RECOVERABLE EXPENSES and the rest of the data?

The code will not extract duplicates of any main, sub, sub-sub categories.

If you want to identify the duplicate items to each specific categories separately, it will be totally different problem and it seems not possible under the structure of the data.

[max3732]

Any other way I can modify the data to fit the code? I know I can put the data on 2 separate sheets, with pre and post NON-RECOVERABLE EXPENSES and it will work, but there has to be a better way

[max3732]

Can anyone help me understand this code? I don't understand the "System.Collections.ArrayList" or the basic logic of how it works.

See if this is how you wanted.

Please Login or Register  to view this content.