Split Excel Cell To Separate Rows

Question

I have a table that was created by a SQL query where each of the results were combined into one cell. I would like to get each value associated correctly on separate rows.

The data is currently set up like this:

    +-------+---------+-------------------------------------------+---------------+

    |   ID  |  Desc   |                  Users                    |   Functions   |

    +-------+---------+-------------------------------------------+---------------+

    |   a   | a desc  | First Last [uname3], First Last [uname45] | abc, def, xyz |
    +-------+---------+-------------------------------------------+---------------+
    |   b   | b desc  | First Last [uname8], First Last [uname72] | lmn, def, xyz |
    +-------+---------+-------------------------------------------+---------------+

I would like for it to be presented as:

    +-------+---------+----------------------+---------------+

    |   ID  |  Desc   |   Users              |   Functions   |

    +-------+---------+----------------------+---------------+

    |   a   | a desc  | First Last[uname3]   | abc, def, xyz |
    +-------+---------+----------------------+---------------+
    |   a   | a desc  | First Last [uname45] | abc, def, xyz |
    +-------+---------+----------------------+---------------+
    |   b   | b desc  | First Last[uname8]   | lmn, def, xyz |
    +-------+---------+----------------------+---------------+
    |   b   | b desc  | First Last [uname72] | lmn, def, xyz |
    +-------+---------+----------------------+---------------+

I would just do this manually but there are ~75 rows with as many as 125 users listed in the same cell.

Thanks for any help!

Tweak your SQL query so that i returns unique record/row for each user — Ronnie Royston, May 24 '16 at 19:52
Unfortunately this data was provided to me, and I don't have access to the db to run my own query. — user2325484, May 24 '16 at 19:55
I've tried finding similar situations online, but the closest I could find was a table to column formula which does not help me associate each user with the correct ID. I've split out the users to separate columns using the Text to Columns function in Excel, but that still doesn't help automate the association to the correct ID. Any help is appreciated. — user2325484, May 24 '16 at 20:08
@user232584, are all the First Last [uname] separated by commas? — Jason Brady, May 24 '16 at 20:15
Yes, they are all separated by commas so that can be used as a delimiter between each user. — user2325484, May 24 '16 at 20:17

Maciej Los · Accepted Answer · 2016-05-27T08:44:50.000

Let's say your data are stored in A to D columns in the sheet named TheNameOfSheet:

Dim i As Integer, j As Integer
Dim users() As String
Dim wsh As Worksheet

Set wsh = ThisWorkbook.Worksheets("TheNameOfSheet")

i = 1 'starting row
Do
    'get the list of users and split it by comma
    users = Split(wsh.Range("C" & i), ", ")
    'go through the list of users
    For j = LBound(users()) To UBound(users())
        'insert new row 
        If j>0 Then wsh.Range("A" & i).EntireRow.Insert(Shift:=xlShiftDown)
       'copy original data
        wsh.Range("A" & i) = wsh.Range("A" & i)
        wsh.Range("B" & i) = wsh.Range("B" & i)
        'insert single name 
        wsh.Range("C" & i) = users(j)
        wsh.Range("D" & i) = wsh.Range("D" & i)
        'increase counter
        i = i + 1
    Next j
    i = i +1   
Loop

For further information, please see:
MS Excel: How to use the SPLIT Function (VBA)
Range.Insert Method (Excel)

Note: I didn't tested this code! I've written it directly from my head ;)

Thanks for your help with this! I kept receiving an overflow error on this macro but was able to find the below post that was able to split up the values by comma. http://stackoverflow.com/questions/8560718/split-comma-separated-entries-to-new-rows — user2325484, May 25 '16 at 13:47

score 1 · Answer 2 · answered May 25 '16 at 06:27

if you are not interested in writing an excel macro. then do this. copy paste the sorted results twice in our excel.

Select the entire column of users, choose text to columns with delimiter as Coma, Sort all the columns (inc/desc, your wish) based on users column

then insert one more row beside between functions and users. paste the below formula =IF(D3=D2,E3,D3) on the first cell of newly added column and drag till bottom.

the output will look like the below image.

remvoe the columns D and E. there you are with the end result as you wanted it

Split Excel Cell To Separate Rows

2 Answers2