transpose columns into rows--with a twist

[us10lh-google]

I've got two columns. Column A contains the ID; column B contains a
list of subjects, delimited by comma. For example:

HappyValley A,B,C,D,E,F


What I'd like to do is to break up the list in column B using
text-to-columns, but then turn each list item into a separate row,
paired with the ID from column A. Thus, the row above should produce:

HappyValley A
HappyValley B
HappyValley C
HappyValley D
HappyValley E
HappyValley F

I'd appreciate some pointers please. TIA.

 

[Pete_UK]

Will you always have 6 items in column B, or is this variable? If so,
what is the max no of items you could expect?

Approx how many rows of data do you have?

Pete

 

[Dave Peterson]

You could use a macro:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim mySplit As Variant
Dim iCtr As Long
Dim HowMany As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1 'no headers
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
mySplit = Split(.Cells(iRow, "B").Value, ",")
HowMany = UBound(mySplit) - LBound(mySplit) + 1
NewWks.Cells(oRow, "A").Resize(HowMany, 1).Value _
= .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Resize(HowMany, 1).Value _
= Application.Transpose(mySplit)
With NewWks.Cells(oRow, "C").Resize(HowMany, 1)
.Formula = "=row()+1-" & oRow
.Value = .Value
End With
oRow = oRow + HowMany
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[fljc]

Hi Pete.

Column B, if it's not empty, will always have 8 items. And there are
about 3000 rows.

I have another requirement: the new rows must contain a third column
showing the relative position of the list item in the original list.
For example, using

HappyValley A 1
HappyValley B 2
HappyValley C 3
HappyValley D 4
HappyValley E 5
HappyValley F 6

Appreciate your help.
Doug