Copying data from merged cell into all resulting cells after splitting

[kwing]

I have a spreadsheet that has been given to me by someone else, and unfortunately it has merged cells in it that are causing some trouble.

I need to have a way to unmerge the merged cells, but for the data in the merged cell to then be copied into each cell that it splits into.

I've attached two spreadsheets - unfortunately I can't really attach the real one since it has research data in it, so I've reduced the size of the file and replaced some of the text. The first file shows how the file starts off, and the file named "result" shows how I would like it to be.

My real file contains some 10,000+ lines of data, and the merged cells are of varying sizes, with no consistency to the size, so I'd like a way to automate this instead of copy and pasting into every empty cell after I unmerge them.

Does anyone have a possible solution?

Thanks

[sweep]

Hi,

This code could be a starting point for you

Please Login or Register  to view this content.

It will unmerge any cells that are currently selected, and then copy the contents of the top, previously merged cell into the empty, previously merged cells.

[kwing]

Great thanks I'll see if I can get that to work.

Otherwise, a colleague has just suggested that I use a formulae something like this

=IF(A2="",A1,A2)

to produce a new column the same as the merged one, but unmerged, and with the blanks filled in.

EDIT: it seems the formulae will only work down to the first empty cell and not the rest, due to my stupidity in formulae

EDIT2: someone in my IT department has just run a script on the file for me on a .csv format of the file, so I have what I need now. Thanks for the advice.

[supriyatno1]

Hi Kwing,

i need to solve that problem too, do you get the solution?

thanx

[jeffreybrown]

@supriyatno1,

This thread is almost three years old and the last activity by Kwing was over two years ago.

If you have a similar problem you might want to start your own thread and if necessary you can refer back to this thread.