Forum Discussion
How to Combine Data in Multiple Lists Separated by Company Info in One Worksheet
From a customer-provided spreadsheet with multiple lists of items and with the same column headers on each list, the issue is that each list is separated by rows containing company and document information data. The individual lists are not tables.
QUESTION
What are some strategies on combining each list into one continuous list?
Thanks,
Clint
@Clint Hill
The file contains equivalent solutions in both Power Query and Excel 365 Lambda functions.
6 Replies
- Hi there,
If each list has a unique ID it’s possible to append the data into one list. Perhaps this could be achieved in PowerBI or Access but if you prefer Excel it might make sense to create a new tab and a copy/paste customer data into it like a manual append. Just throwing ideas out there….
Kind regards,
Julie Bunavicz@Clint Hill
The file contains equivalent solutions in both Power Query and Excel 365 Lambda functions.
- Example files are worth millions!!! Thanks Peter!
- Thank you Julie! I will review any unique IDs (possibly use the item piece mark column) and resort to the manual copy/paste workaround that I had hoped to avoid. Our users preference and cloud environment setup mean keeping this in Excel.
Thanks,
Clint HillWhether it is PQ or Excel formulas, the first thing you need to be able to do is distinguish between header rows and data.
In the image, I used the fact that the value field is blank on the group header rows. The next step (PQ or Excel) is to fill down the company names to a fresh column. Finally filter out the original group headers and other blanks.
= LET( company, IF(ISBLANK(Value), ID), filldown, SCAN("", company, LAMBDA(prior,company, IF(ISTEXT(company), company, prior)) ), extended, HSTACK(filldown, table), FILTER(extended, ISNUMBER(Value)) )
