CODEWITHSUNDEEP
excelexcel-formularowsrepeatcsv
Siglid
Siglid

Reputation: 13

How do I split out delimited data in one cell and repeat the adjacent cell against each item of delimited data separated out

I have an excel file with a list of applications in one cell, and multiple delimited user names against the application in the adjacent cell. I need to separate the users from one cell to multiple cells, in one column, while repeating the name of the application for each user

    Current data looks something like this;

    Column1        | Column 2
    Application 1  | User1,User2,User3
    Application 2  | User1,User2,User3

    I want to get an output to be something like this;
    Column 1       | Column 2   
    Application 1  |  User 1
    Application 1  |  User 2
    Application 1  |  User 3
    Application 2  |  User 1
    Application 2  |  User 2
    Application 2  |  User 3

I have been playing about with index match, VBA etc and failing miserably - I don't think any code I've completed to date is relevant

Upvotes: 1

Views: 687

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Here is a super simple approach:

Sub Reorg()
    Dim i As Long, N As Long, ap As String, arr, a
    Dim k As Long

    N = Cells(Rows.Count, "A").End(xlUp).Row
    k = 1

    For i = 1 To N
    ap = Cells(i, 1).Value
    arr = Split(Cells(i, 2).Value, ",")
        For Each a In arr
            Cells(k, 3).Value = ap
            Cells(k, 4).Value = a
            k = k + 1
        Next a
    Next i
End Sub

For original data in columns A and B:

enter image description here

Upvotes: 1

Related Questions