Need to transpose multiple comma separated values

SRd240sx

New Member
Joined
Dec 19, 2008
Messages
11
Hey guys, I have a list of comma separated values. I would like to change these to tab separated values, and then transpose them so that they are all displayed in one column. Does anyone know if it's possible to do this in Excel 2007?

Example:

Date | Keywords
1/01/01 | shoe, shoes, clothes, clothing
1/02/01 | shirt, shirts, jacket, jackets

I need the Keywords column to display each value separately, and in one column. I also need each row to have the original date information. So I need it to look like this:

Date | Keywords
1/01/01 | shoe
1/01/01 | shoes
1/01/01 | clothes
1/01/01 | clothing
1/02/01 | shirt
1/02/01 | shirts
1/02/01 | jacket
1/02/01 | jackets

Can anyone help out?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
I would use a macro to do this:
Code:
Option Explicit

Sub SplitKeywords()
'JBeaucaire  (12/7/2009)
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = LR To 2 Step -1
        MyArr = Split(Range("B" & i), ", ")
        Range("B" & i) = MyArr(0)
        For v = 1 To UBound(MyArr)
            Rows(i + v).Insert xlShiftDown
            Range("A" & i + v) = Range("A" & i)
            Range("B" & i + v) = MyArr(v)
        Next v
    Next i

Application.ScreenUpdating = True
End Sub

=========
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select SplitKeywords from the macro list.
 

SRd240sx

New Member
Joined
Dec 19, 2008
Messages
11
jbeaucaire, this is excellent, and can potentially save me HOURS of work. THANK YOU.

Let me try this out on my spreadsheet and I'll report back to see whether it works or not. The data I have is not exactly like the example I gave.. there are a few more columns and the # of keywords can vary (it's not just 4 keywords). I hope it works!
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
This macro works by adding rows to "insert room" for the values that are split out of the column B comma-delimited strings. IF there is more columns of data they won't be touched, but they also won't be duplicated either since the macro is designed to match your example.

You could change this one line of code:
Code:
            Range("A" & i + v) = Range("A" & i)
...to encompass more columns of data to "copy down". Right now it is only duplicating the column A values, but if you wanted it to duplicate all the way over to column D, then you could change it to this:
Code:
            Range("A" & i + v, "D" & i + v) = Range("A" & i, "D" & i)

Makes sense?
 

SRd240sx

New Member
Joined
Dec 19, 2008
Messages
11

ADVERTISEMENT

This macro works by adding rows to "insert room" for the values that are split out of the column B comma-delimited strings. IF there is more columns of data they won't be touched, but they also won't be duplicated either since the macro is designed to match your example.

You could change this one line of code:
Code:
            Range("A" & i + v) = Range("A" & i)
...to encompass more columns of data to "copy down". Right now it is only duplicating the column A values, but if you wanted it to duplicate all the way over to column D, then you could change it to this:
Code:
            Range("A" & i + v, "D" & i + v) = Range("A" & i, "D" & i)

Makes sense?

I should have given an example from the actual spreadsheet I'm working with. (BTW the macro you provided works just fine w/ my example).

I actually have 4 columns: Date, User, Action, Keyword

I need columns 1-3 duplicated, and column 4 (Keyword) split. I tried changing the line of code but it didn't work.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Sometimes I need to include .Value and sometimes I don't, I haven't figured out 100% which is which yet, so have to just test it...
Rich (BB code):
Option Explicit

Sub SplitKeywords()
'JBeaucaire  (12/8/2009)
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = LR To 2 Step -1
        MyArr = Split(Range("D" & i), ", ")
        Range("D" & i) = MyArr(0)
        For v = 1 To UBound(MyArr)
            Rows(i + v).Insert xlShiftDown
            Range("A" & i + v, "C" & i + v).Value = Range("A" & i, "C" & i).Value
            Range("D" & i + v) = MyArr(v)
        Next v
    Next i

Application.ScreenUpdating = True
End Sub
 

SRd240sx

New Member
Joined
Dec 19, 2008
Messages
11

ADVERTISEMENT

I'm getting Run-time error '9':
Subscript out of range

Is this because I have too many keywords in the Keyword column? Sometimes this column can contain hundreds of thousands of comma separated values, it seems like this will be a problem.

**EDIT: I just tried a test where the keyword column only contains 4 or fewer keywords, but I am still getting this error message?
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
It's working for me, so I can't see what's stopping it for you.

Post your sheet somewhere I can download, post a link here....I'll take a look.

NOTE: Excel 2003 only has 65k rows, so hundreds of thousands of commas in one cell, that ain't gonna work.
 
Last edited:

SRd240sx

New Member
Joined
Dec 19, 2008
Messages
11
I think i created too many tabs or something, I opened up a new Excel .xlsm and redid it with the new code and it works!

BTW I'm using Excel 2007, albeit it is a bit slow w/ all these rows. Gonna try it out tomorrow (I've just been using tests). Thanks so much jbeaucaire, you've been extremely helpful :)
 

SRd240sx

New Member
Joined
Dec 19, 2008
Messages
11
jbeaucaire, I ended up needing to add 4 more columns. 3 of them need to be duplicated, it now looks like this:

Account, Date, User, Campaign, AdGroup, ACTION, KEYWORDS, Description. I need KEYWORDS to be split, and Description not to be duplicated, and everything else duplicated. I tried modifying the macro and was somewhat successful (I got it to do what I just described), but it gives me a runtime error 13, Type mismatch. The entire document looks ok, but I'm not exactly sure why it's giving me an error or what it means. Can you take a look?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top