Need help to Transpose column into unique rows with comma delimited values

kdevine321 · Nov 4, 2013

Hi,
I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was http://www.mrexcel.com/forum/excel-...ranspose-multiple-comma-separated-values.html

I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:

GA-AG-00010-A-2013

Apache Hunting Club

709

36

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.

Sub SplitKeywords()
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) = Range("A" & i, "C" & i).Value
Range("D" & i + v) = MyArr(v)
Next v
Next i

Application.ScreenUpdating = True
End Sub

Any ideas on how to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.

Thanks!

hiker95 · Nov 4, 2013

kdevine321,

Welcome to the MrExcel forum.

Sample raw data:

Excel 2007

A

B

C

D

1

2

GA-AG-00010-A-2013

Apache Hunting Club

709

36

3

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

4

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

5

6

7

8

9

10

11

Sheet1

After the macro:/b]

Excel 2007
A B C D
1
2 GA-AG-00010-A-2013 Apache Hunting Club 709 36
3 GA-AG-00020-A-2013 Tiger Branch Hunt Club 1596 71
4 GA-AG-00020-A-2013 Tiger Branch Hunt Club 1596 72
5 GA-AG-00030-A-2013 Big "O" Hunting Club 3058 59
6 GA-AG-00030-A-2013 Big "O" Hunting Club 3058 64
7 GA-AG-00030-A-2013 Big "O" Hunting Club 3058 65
8 GA-AG-00030-A-2013 Big "O" Hunting Club 3058 75
9 GA-AG-00030-A-2013 Big "O" Hunting Club 3058 79
10 GA-AG-00030-A-2013 Big "O" Hunting Club 3058 84
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit Sub ReorgData() ' hiker95, 11/04/2013 ' http://www.mrexcel.com/forum/excel-questions/737089-need-help-transpose-column-into-unique-rows-comma-delimited-values.html Dim r As Long, lr As Long, s lr = Cells(Rows.Count, 1).End(xlUp).Row For r = lr To 2 Step -1 If InStr(Trim(Cells(r, 4)), ",") > 0 Then s = Split(Trim(Cells(r, 4)), ", ") Rows(r + 1).Resize(UBound(s)).Insert Cells(r, 1).Resize(UBound(s) + 1, 3).Value = Cells(r, 1).Resize(, 3).Value Cells(r, 4).Resize(UBound(s) + 1).Value = Application.Transpose(s) End If Next r End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

hiker95 · Nov 4, 2013

kdevine321,

My actual data spans from column A-Q and can place the "split values" in column Q.

My column D should be column Q?

kdevine321 · Nov 4, 2013

Thanks hiker95! This code does work on a subset of my data, so that's a huge start. And to answer your question, yes, my example data Column D is actually column Q in the real dataset I'm trying to manipulate. Unfortunately I can't understand your code enough to expand it out to Q. Could you help with this as well, maybe even put in a few comment lines to help me understand the logic?

hiker95 · Nov 4, 2013

kdevine321,

Is this what your actual raw data looks like?

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Titles A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Title Q

2

GA-AG-00010-A-2013

Apache Hunting Club

709

36

e

f

g

h

i

j

k

l

m

n

o

p

3

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

e

f

g

h

i

j

k

l

m

n

o

p

4

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

5

Sheet1

hiker95 · Nov 4, 2013

kdevine321,

Or, is this what your actual raw data looks like?

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Titles A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Title Q

2

GA-AG-00010-A-2013

Apache Hunting Club

709

e

f

g

h

i

j

k

l

m

n

o

p

36

3

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

e

f

g

h

i

j

k

l

m

n

o

p

71, 72

4

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

e

f

g

h

i

j

k

l

m

n

o

p

59, 64, 65, 75, 79, 84

5

Sheet1

kdevine321 · Nov 4, 2013

Correct.

hiker95 · Nov 4, 2013

kdevine321,

Correct on my reply #5 or #6?

kdevine321 · Nov 4, 2013

Oops, Correct on Reply #5. Must have replied before the refresh came through.

hiker95 · Nov 4, 2013

kdevine321,

Sample raw data:

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Titles A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Title Q

2

GA-AG-00010-A-2013

Apache Hunting Club

709

36

e

f

g

h

i

j

k

l

m

n

o

p

3

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

e

f

g

h

i

j

k

l

m

n

o

p

4

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

5

6

7

8

9

10

11

Sheet1

After the macro:

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Titles A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Title Q

2

GA-AG-00010-A-2013

Apache Hunting Club

709

36

e

f

g

h

i

j

k

l

m

n

o

p

36

3

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

e

f

g

h

i

j

k

l

m

n

o

p

71

4

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

e

f

g

h

i

j

k

l

m

n

o

p

72

5

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

59

6

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

64

7

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

65

8

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

75

9

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

79

10

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

e

f

g

h

i

j

k

l

m

n

o

p

84

11

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:

Option Explicit
Sub ReorgDataV2()
' hiker95, 11/04/2013
' http://www.mrexcel.com/forum/excel-questions/737089-need-help-transpose-column-into-unique-rows-comma-delimited-values.html
Dim r As Long, lr As Long, s
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Trim(Cells(r, 4)), ",") = 0 Then
    Cells(r, 17).Value = Cells(r, 4).Value
  ElseIf InStr(Trim(Cells(r, 4)), ",") > 0 Then
    s = Split(Trim(Cells(r, 4)), ", ")
    Rows(r + 1).Resize(UBound(s)).Insert
    Cells(r, 1).Resize(UBound(s) + 1, 16).Value = Cells(r, 1).Resize(, 16).Value
    Cells(r, 17).Resize(UBound(s) + 1).Value = Application.Transpose(s)
  End If
Next r
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.

Need help to Transpose column into unique rows with comma delimited values

kdevine321

New Member

Excel Facts

hiker95

Well-known Member

hiker95

Well-known Member

kdevine321

New Member

hiker95

Well-known Member

hiker95

Well-known Member

kdevine321

New Member

hiker95

Well-known Member

kdevine321

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Need help to Transpose column into unique rows with comma delimited values

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock