1

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

Siglid
  • 13
  • 2
  • 1
    Possible duplicate of [Expand a comma separated table in multiple rows](https://stackoverflow.com/questions/32469160/expand-a-comma-separated-table-in-multiple-rows) – JNevill Feb 18 '19 at 15:52
  • 1
    https://stackoverflow.com/questions/37956019/expand-table-rows-for-each-delimited-value-in-one-columns-cells – JNevill Feb 18 '19 at 15:52
  • https://stackoverflow.com/questions/8560718/split-comma-separated-entries-to-new-rows – JNevill Feb 18 '19 at 15:53
  • In short, there is no super great way to do this. The first link is a manual way of doing it. The second link is using power bi. And the third is an ugly VBA way of doing it. – JNevill Feb 18 '19 at 15:54
  • 1
    I would loop with VBA but only if this is an ongoing need. If it's a one-off analysis then many of what @JNevill are listing here will suffice. – n8. Feb 18 '19 at 15:54
  • Thanks N8, it's going to need repeated, so I'll give it a go with the VBA first, and if that is not going to work, I'll maybe try the other two. Thanks for the answers – Siglid Feb 18 '19 at 16:12

1 Answers1

1

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

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Fabulous! That worked perfectly. A small question (just out of interest and to understand this better) say I had a couple of more columns that I wanted to treat the same as the application column, in the sense it's repeated based on the Users in the user column, how would this affect the code? Column 1 | Column 2 | Column 3 | Column 4 Manager| Source 1 |Application 1 | User1,User2 – Siglid Feb 18 '19 at 21:18
  • @Siglid It would be a very easy change............as you see, the code is quite small. – Gary's Student Feb 18 '19 at 22:36