Forum Discussion
Solved
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 info...
@Clint Hill
The file contains equivalent solutions in both Power Query and Excel 365 Lambda functions.
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
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
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 Hill
Thanks,
Clint Hill
Whether 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)) )- Thanks for your interest, logic, and your programmatic approach all given here.
Clint