Forum Discussion
SimonYoung
Mar 23, 2023Copper Contributor
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 ...
mathetes
Mar 23, 2023Silver Contributor
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.
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.
SimonYoung
Mar 23, 2023Copper Contributor
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
- mathetesMar 23, 2023Silver Contributor
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
- SimonYoungMar 23, 2023Copper Contributormathetes Thanks so much for this - this will save me a lot of effort. Cheers
- SimonYoungMar 23, 2023Copper ContributorI meant to say that the data in the image is anonymised.