Forum Discussion
Unmerge cells and copy values in a supplier report
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
- Are you in a position to go to that travel agent -- you are the client/customer, after all -- and insist that the agent give you the data in the manner you need it. That agent has made it "look nice" but in the process interfered in monstrous ways with functionality. He or she needs to be told that it's not how you want it.
That said, please remove the image --which contains real names--and either block them out (they're not necessary for your request) or replace them with fictitious names. Then we can proceed to see how you can fix it. But I'm quite serious: go back to the travel agent and request that it be done properly to enable you to do your job.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
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
