Mar 22 2023 05:40 PM
Hi All - Excel novice here.
I have a report from a corporate travel agent that I would like to alter. Currently, some fields relating to a booking are merged - common info - booking no., name, cost centre etc. These are merged across several lines, each line showing a sector or activity (e.g. flight1, flight2, car hire, hotel etc.). Each sector has a cost associated with it. The number of merged cells varies by booking and number of sectors booked.
I'd like to be able to unmerge the cells associated with each booking number so that I can cost detail by airline/hotel, traveler., cost center. there
Mar 22 2023 05:57 PM
Mar 22 2023 08:32 PM
@mathetes Thanks for responding. The data has been replaced and 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. Cheerss
Mar 22 2023 08:33 PM
Mar 23 2023 07:30 AM - edited Mar 23 2023 07:32 AM
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
Mar 23 2023 01:58 PM