Seperate by comma and stack

Hi,
I have attached a spreadsheet where i would like the names in Column D to be split out and stacked on top of each other

In the tab example i have laid this out. So when we split the names and stack them, i would like to keep the data from the adjacent columns for each new split

So the process is to split out the names and keep the data from cells A,B,C+E

Any help would be greatly appreciated!

Thanks
Seamus
test.xls
Seamus2626Asked:
Who is Participating?
 
StephenJRCommented:
Try this Seamus:
Sub x()

Dim v, w, i As Long, j As Long, n As Long

With Range("A1", Range("E" & Rows.Count).End(xlUp))
    v = .Value
    .ClearContents
End With

For i = LBound(v, 1) To UBound(v, 1)
    n = Range("A" & Rows.Count).End(xlUp).Row + 1
    If InStr(v(i, 4), ",") > 0 Then
        w = Split(v(i, 4), ",")
        For j = LBound(v, 2) To UBound(v, 2)
            Range("A" & n).Offset(, j - 1).Resize(UBound(w) + 1) = v(i, j)
        Next j
        Range("D" & n).Resize(UBound(w) + 1) = Application.Transpose(w)
    Else
        For j = LBound(v, 2) To UBound(v, 2)
            Range("A" & n).Offset(, j - 1) = v(i, j)
        Next j
    End If
Next i

End Sub

Open in new window

0
 
StephenJRCommented:
I have to say it isn't clear to me what you are asking.
0
 
Seamus2626Author Commented:
Hey Stephen,

Where there is multiple names in a cell (in Col D), i need these names to be cut, a new row inserted and the name pasted into Col D of the new row.

I can then use formula to get matching data

The first thing i need though is to seperate the names in Col D (Where there is Multiples e.g. D7) and then insert a row, or two, or three (dependant on how many names, in D7, one new row) and then paste the multiple name in, with D7, Michael Finance would be cut and pasted into the new row(D8)

Hope thats clearer!
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
StephenJRCommented:
OK, I see now. I was sidetracked by the red cells - are they excluded from this operation because they seem to be the same in both sheets?
0
 
Seamus2626Author Commented:
Sorry my error, they are included and should be seperated (which i didnt do in tab Example)

Cheers
0
 
Seamus2626Author Commented:
Perfect Stephen, very good!!

Out of interest, how does that code work, i see it clears contents, where does it get the information back from?
0
 
StephenJRCommented:
It puts the range into an array before deleting the contents and then the array is transferred back to the worksheet.
0
 
Seamus2626Author Commented:
Thanks Stephen!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.