Query about extracting multiple values from a cell into new rows...

[viper911]

I have no idea if this can even be done but I figured I'd sign up and ask

If you see the sample below, I want to turn the left hand bit (cols A, and B) into the right hand bit (cols E, F).
They are all numeric and col B can have a varying number of values up to a maximum of 5


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 104px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">100,101,102,103</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">101,102,103</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">101</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">102</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">103</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">101</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">102</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">103</TD></TR></TBODY></TABLE>

I'm not sure if it's even feasible to do anything like this but I thought I'd ask anyway (there are about 400 rows in the existing row A so not something I really want to be doing by hand if at all possible)

Thanks...

 

[VoG]

Try

Sub splt()
Dim LR As Long, i As Long, j As Long, X
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    X = Split(Range("B" & i).Text, ",")
    For j = LBound(X) To UBound(X)
        Range("E" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & i).Value
        Range("F" & Rows.Count).End(xlUp).Offset(1).Value = X(j)
    Next j
Next i
End Sub

 

[viper911]

Well I'm not going to pretend I understand any of that but it worked!

Many many thanks

 

[Peter_SSs]

A slight variation to consider where the data is written in blocks rather than a row at a time. May be a difference in performance if the data set is large.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> viper911()<br>    <SPAN style="color:#00007F">Dim</SPAN> ub <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> X<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    nr = 1<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp))<br>        X = Split(c.Offset(, 1).Text, ",")<br>        ub = <SPAN style="color:#00007F">UBound</SPAN>(X) + 1<br>        <SPAN style="color:#00007F">With</SPAN> Cells(nr, "E").Resize(ub)<br>            .Value = c.Value<br>            .Offset(, 1).Value = Application.Transpose(X)<br>            nr = nr + ub<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>