Need to transpose multiple comma separated values

[SRd240sx]

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?

 

[jbeaucaire]

I would use a macro to do this:

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]

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]

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:

            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:

            Range("A" & i + v, "D" & i + v) = Range("A" & i, "D" & i)

Makes sense?

 

[SRd240sx]

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:

            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:

            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]

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...

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]

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]

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.

 

[SRd240sx]

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]

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?