Forum Discussion
Unmerge cells and copy values in a supplier report
The data ... is anonymised. I've been back to our agent and have asked them to provide the report in a more usable format but that will take them some time
So in the meantime, it turns out that there's a relatively simple formula to fill in those rows. You seem pretty sophisticated with Excel, so I've attached a simple example. What I'd do is put in "helper columns" to be used temporarily to fill in what are those blanks due to the merging of cells. You could put those "helper columns" adjacent to each of the columns with merged cells, or do them as a set of seven to match the seven "offending" columns from the travel agent.
Then use this formula (adjusted for the appropriate column references) and copy it down the entire helper column.
=IF(B3="",D2,B3)
Where B is the column with those merged cells and D is the column that you're using to create entries for each row.
What this does is look at the column in which cells have been merged -- where Excel still "sees" B4 and B5, etc., even though they've been visually merged -- and if that cell is actually blank it uses the value above; but when it hits a new name (as well as at the very start, where there's a name), it inserts that name. Anyway, that works.....try it out.
See the example file below
