duranimal86
New Member
- Joined
- Jul 24, 2019
- Messages
- 18
- Office Version
- 365
- Platform
- Windows
I have done some basics with Power Query, but I have a need that is beyond my limited skills. So I am turning to my favorite experts here for some help. I have created multiple workbooks with analytics where users input descriptions and amounts for the different accounts. There are 6 identical workbooks, 1 for each Company, and I need to create a summary to view the all of the info from all of the workbooks for review at the consolidated level. So instead of having to flip between the 6 workbooks, I would like to have them lined up and displayed horizontally (Company A, Company B, Company C, etc...). Each of the workbook sheets have the same columns and the same list of accounts (1-32), but there could be different numbers of rows per Account depending on how many explanations were added for that particular account.
So as an example below shows 3 different Companies with the same columns and the same #s, but with different amount of rows for each #.
I basically want to align everything by the Account #s and end up with something similar to this:
I would also need a way to include the different Company Names to each section, either something like Appending them to the Column names or as a header over that particular section/group. Hopefully that all made sense. I appreciate any help or guidance on this. Also, I am not completely set on using PQ if there are other suggestions, that just seemed like the best option in this case.
So as an example below shows 3 different Companies with the same columns and the same #s, but with different amount of rows for each #.
| CompanyA | CompanyA | CompanyA | CompanyB | CompanyB | CompanyB | CompanyC | CompanyC | CompanyC |
| # | Description | Amount | # | Description | Amount | # | Description | Amount |
1 | 1 | 1 | ||||||
1 | 2 | 1 | ||||||
2 | 2 | 1 | ||||||
3 | 2 | 2 | ||||||
3 | 3 | 2 | ||||||
3 | 3 | 3 | ||||||
4 | 4 | 4 | ||||||
4 | 4 | 4 | ||||||
5 | 5 | 5 | ||||||
5 | 5 | |||||||
5 |
I basically want to align everything by the Account #s and end up with something similar to this:
| # | CompanyA.Description | CompanyA.Amount | CompanyB.Description | CompanyB.Amount | CompanyC.Description | CompanyC.Amount |
1 | A1 | $ | B1 | $ | C1 | $ |
1 | A1 | $ | C1 | $ | ||
1 | C1 | $ | ||||
2 | A2 | $ | B2 | $ | C2 | $ |
2 | B2 | $ | C2 | $ | ||
2 | B2 | $ | ||||
3 | A3 | $ | B3 | $ | C3 | $ |
3 | A3 | $ | B3 | $ | ||
3 | A3 | $ | ||||
4 | A4 | $ | B4 | $ | C4 | $ |
4 | A4 | $ | B4 | $ | C4 | $ |
5 | A5 | $ | B5 | $ | C5 | $ |
5 | B5 | $ | C5 | $ | ||
5 | C5 | $ |
I would also need a way to include the different Company Names to each section, either something like Appending them to the Column names or as a header over that particular section/group. Hopefully that all made sense. I appreciate any help or guidance on this. Also, I am not completely set on using PQ if there are other suggestions, that just seemed like the best option in this case.
