July 11, 2016
Contender for longest topic name?
Ok what I am trying to do is consolidate worksheets within the same workbook. Within each worksheet there are sections for Profit & Loss, Balance Sheet and Cashflow statements. The complications with the consolidation are listed below. I know that if we started again the obvious solution would be to re-structure the worksheets but I am afraid that is not an option in this scenario:
- The worksheets do not use any names ranges or tables so the statements can't be selected independently.
- Each sheet has various other statements within them. These statements are not uniform across the sheets. They are inserted above where the Profit & Loss statement is in the sheet which means that the Profit & Loss starts in a different row number in each sheet.
- There are rows with the same name within the different types of statements. For example, "Depreciation" appears in the Profit & Loss and also in the Cashflow. I was hoping to use a reference table that I could link to the relevant statement line but the duplicated use of the same name causes a problem here.
I have attached an example file that hopefully illustrates what I am trying to do. It shows three tabs that I want to consolidate, Comp1, Comp2 and Comp3. I want to get the data in to one table. In that table it will show columns for Statement (Profit & Loss, Balsht etc), the statement section and sub-section, and statement line. It will then report the values for the months.
There is another sheet in the file, Report_Category, which shows the categorisations required. This is the table I was hoping to use to lookup against the statement lines in the sheets. However as mentioned the statements start in different rows, row 15 in Comp1, row 20 in Comp2 and row 25 in Comp3. Not only that but the lines have the same names within each statement. I have highlighted the "Depreciation" line in the Profit & Loss and Cashflow as an example. If you look at the Report_Category sheet I have shown in red all of the lines affected.
So is there any way to do this? If it was simple Excel I would probably do some IF statement that said something like if previous line equals "Exceptional Items" then statement equals Profit & Loss otherwise Cashflow but I don't know if this is possible with PQ.
As always your help is greatly appreciated. If my explanation doesn't make sense please come back with any questions and I will try and clarify.
July 16, 2010
You can use the get multiple files containing multiple sheets with Power Query technique to get the unstructured data.
Then add a custom column that checks if the text in Column1 contains Profit & Loss, Cash Flow or Balance Sheet Statement, if it does, then bring in the label. You can then fill that label down so that you know which rows relate to which statement. See example attached. Note: you'll need to edit the data source to point to your Consol_Example.xlsx folder location.
Hopefully you can take it from there.
July 11, 2016
Thanks for the reply. That works a treat. I am never sure whether to start the query from a folder or a file.
Can I just take this opportunity to say that I find your forum probably the best one out there. The way is is administered and that you personally take the time to reply to posts is a credit to you.
Happy holidays to to you and all members and let's hope for a better 2021 for everyone.