How can I split merged cells and distribute the contents of the merged cells so that each unmerged cell has the contents of it's parent merged cell?

I have a large spreadsheet of data with a mix of merged cells and individual cells (e.g. column A contains large merged cells (A2:A4999) indicating a primary location, column B contains smaller merged cells (B2:B299) indicating a sub-location, column C contains individual cells (C2) indicating equipment in that specific location). In order to make best use of the data (either sorting, filtering, or using pivots) I would like to un-merge each merged cell and distribute the text to the resulting split cells (e.g. each un-merged cell in column A would be filled with the primary location, etc.).
adrian2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aj8787Commented:
Select the cell, the range of cells, or the entire column that contains the text values that you want to divide across other cells. A range can be any number of rows tall, but no more than one column wide.
 Important   Unless there are one or more blank columns to the right of the selected column, the data to the right of the selected column will be overwritten.

On the Data menu, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

You can swap rows and columns words in solution..

Is this what you are looking for?
0
adrian2010Author Commented:
Thanks for your answer, unfortunately I think your answer is for splitting something inside cells using some character that is in all the cells to split, for example to split a name contained into one cell (John Doe) into two cells one with (John) and the other with (Doe).

What I need to do is the following: If I have 3 cells in a column that are merged and the merged cell contains the name John Doe, I would like to split the cell but instead of getting only one John Doe in the upper cell I would like each of the three cells filled with John Doe. That is the most basic description for the problem, the full problem is better explained in the original question.

Thanks again and I hope you can help me.

Adrian
0
kittenwhiskyCommented:
select the 3 merged columns, right click on selection, goto Format Cells option, select Alignment tab, click on merge cells option until the check box is empty.

This will unmerge the columns, keeping their values in the left column only. Copy & paste the left column into the second & third column.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

kittenwhiskyCommented:
I think I understand your issue now:

Some rows have merged columns (ie merged columns A to C, or B to C) and some of them aren't?
So when you unmerge all the rows, you get some empty cells in columns B & C?

If I've understood you correctly, do the following:

select all the data (merged & unmerged rows), unmerge all of them (goto Format Cells option, select Alignment tab, click on merge cells option until the check box is empty).

run the macro below to fill in empty cells (if cells B & C are empty, the macro fills them with values from column A, if only cells C are empty, it is filled with values from cells B).

Is this what you are looking for?

Sub FillInBlanks()
Dim rng As Range, i As Double
Set rng = Selection
For i = 1 To rng.Cells.Count
    If IsEmpty(rng.Cells(i)) Then
        If rng.Cells(i).Column <> rng.Columns(1).Column Then
            rng.Cells(i) = rng.Cells(i).End(xlToLeft)
        End If
    End If
Next i
End Sub

Open in new window

0
adrian2010Author Commented:
Hi! kittenwhisky

Thanks for your answer, unfortunately it's now what I need.

I'll try to be more specific using a much simple example

-In will use only one column
-I am using 1500 rows
-I have 500 vertically merged cells, each merged cell spans 3 rows (In the real problem the merged cells spans ARE VARIABLE this is just a way to illustrate the problem).
-Each merged cell has a the name of a different person (in the real problem there are also numbers and punctuation) so I have 500 names in 500 merged cells and since each merged cell spans 3 rows I am using 1500 rows.

What I need is to unmerge the 500 cells so that I will get 1500 normal cells but each unmerged cell will be replaced by three normal cells WITH THE NAME OF THE PERSON that was in the original cells.
So for each person I will get 3 cells (each in a row) that will replace the original merged cell and each cell will have the name of the person that was in it's original merged cell.

Thanks again and I hope you can help me.

Adrian
0
kittenwhiskyCommented:
thanks for clarifying,

pls do the following for each column:

select the rows of the column in question, unmerge them all as described above, and use the macro below to fill in blanks (it will take the value from the first non-blank row above it).
Sub FillInBlanks()
Dim rng As Range, i As Double
Set rng = Selection
For i = 1 To rng.Rows.Count
    If IsEmpty(rng.Cells(i)) Then
        If rng.Cells(i).Row <> rng.Rows(1).Row Then
            rng.Cells(i) = rng.Cells(i).End(xlUp)
        End If
    End If
Next i
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kittenwhiskyCommented:
forgot to mention: don't forget to select the data in the particular row before running the macro.
0
kittenwhiskyCommented:
forgot to mention: don't forget to select the data in the particular COLUMN before running the macro.
0
Aj8787Commented:
I got your problem completely.

I ran a small version with three merged cells spanning three rows. (9 cells in total)

I unmerged one by one i got 3 filled cells and 6 unfilled and i dragged and copied but this is not what you want because you definitely dont want to do it manually so you need a macro for this for sure.

kittenwhisky macro looks effective to me though
0
kittenwhiskyCommented:
sorry for multiple posts, I tweaked the macro just a little to allow for multiple column selection. So you can select range A2:C4999 once, unmerge all cells, and whilst the selection is still active, run the macro. It should fill in all the blanks for all columns correctly.
Sub FillInBlanks()
Dim rng As Range, i As Double
Set rng = Selection
For i = 1 To rng.Cells.Count
    If IsEmpty(rng.Cells(i)) Then
        If rng.Cells(i).Row <> rng.Rows(1).Row Then
            rng.Cells(i) = rng.Cells(i).End(xlUp)
        End If
    End If
Next i
End Sub

Open in new window

0
adrian2010Author Commented:
The solutions needs to be followed in several steps, but it works fine. Also I had never used excell marcros code before. But I guess that is my bad
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.