[Solved] Duplicating split-cell contents into merged cells?

[dzurn]

I had inherited a spreadsheet which had a merged cell (part number) in Column A next to several unmerged rows in Column B (description), C (price) etc.

I needed to split and sort the spreadsheet based on Column B's contents, but sorting the merged cells ended up losing the part number in Column A, so many rows had no part #. Unsatisfactory. I was looking for a way to "split" a cell so that the contents were duplicated in all the component cells.

Current structure:

Code: Select all

|ColA | Col B     | Col C  |
|     |Property 1 | Value 1 |
|_PN_ |Property 2 | Value 2 |
|     |Property 3 | Value 3 |
...

Desired Result:

Code: Select all

|ColA | Col B     | Col C  |
|_PN_ |Property 1 | Value 1 |
|_PN_ |Property 2 | Value 2 |
|_PN_ |Property 3 | Value 3 |
...

The only thing I found was that, since I had a blank row after each merged group, I was able to select the merged cell and the cell in the blank row below, and do Fill>Down. Repeat for each group. That seemed to work so that after unmerging the entire column A, I had duplicate part numbers in each row, so now I could sort on the descriptions. (One caveat: some of the Part Numbers ended in numbers, so I had to be sure to hold down the Cmd/Control key so that OOo didn't try to increment the part number as it filled them down.)

As I will definitely need to repeat this process on the sheet that I don't own, I'm trying to figure out if there's a better way to accomplish this same result without the labor-intensive hand editing. What I would like is to know if there's a way to select the entire column and have some kind of macro that would investigate each merged cell and copy the contents into each component cell, and then split the cell.

Thanks
Darryl

[squenson]

The content of a merged cell is stored in the top left cell of the merged range, so you can create a new column testing the value. I attach a quick example. Then you can copy, paste special (values) the content of this new column, then un-merge your cells and use the new column for sorting. This should save 95% of the manual work!

[dzurn]

The content of a merged cell is stored in the top left cell of the merged range, so you can create a new column testing the value. I attach a quick example. Then you can copy, paste special (values) the content of this new column, then un-merge your cells and use the new column for sorting. This should save 95% of the manual work!

That works, thanks! I realized after looking at your solution that I just need to refer to the previous result if nothing was in the desired cell.

It also reminded me that I really would like to be able to use the R1C1 notation so that these kinds of formulas become much more obvious. Please vote for this issue, which hasn't seen any visible movement since 2008:
http://www.openoffice.org/issues/show_bug.cgi?id=10864

Thanks
Darryl