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.
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