• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 4
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Seamus2626Author Commented:
Sorry my error, they are included and should be seperated (which i didnt do in tab Example)

Cheers
0
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now