Combining data that is split with a name row within the same sheet

Hard to explain so I am attaching sample data. Basically a file that is split with different company names. What is the easiest/fastest way to combine them so the final output looks like the one in the 'Output' tab of the attached sample file?

Thank you in advance.

Test Data.xlsx

Tips and Tricks

Accepted answers

ntakeda

@sowjanyayinti

following steps:

Use the Multi-Row Formula tool to retrieve the company name when "Number" is null. Otherwise, retrieve the company name from the previous row.
Filter out rows where "Number" is null.

Please check the workflow for details.

New Workflow20250314.yxzp

All comments

KGT

OK, so normally I would tag lines and then formulas to work out which elements go where in the final table... (not complicated, but a few tools)

But, in this case you just have a filter on !IsEmpty([Date]) and get your table?

sowjanyayinti

But how do i get the company name into a column of its own for the rows it is specific to? Cross tab and transpose?

ntakeda

@sowjanyayinti

following steps:

Use the Multi-Row Formula tool to retrieve the company name when "Number" is null. Otherwise, retrieve the company name from the previous row.
Filter out rows where "Number" is null.

Please check the workflow for details.

New Workflow20250314.yxzp

sowjanyayinti

Thank you @ntakeda. That worked. Multi row formula... did the trick.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703