How to transpose the below type of data in Excel 2003?

Question

1
2,3
4,5,6
7,8
9,10

i want to transpose the above data as below

1
2
3
4
5
6
7
8
9
10.

Can any one explain the procedure using advanced Excel or a Macro? Thanks.

Answer 1

With the original values in A1:A5, select A1:A5. This macro reads the values into a variant array. Due to the nature of the read, the values need to be transposed as they are read into the variant. It then cycles through each and attempts to split each value at the comma into two or more values. If it doesn't find a comma, it just uses the original cell's value in the next available cell. If it has split the original value it cycles through each of those, putting them into the next available cell.

Sub reorganize()
    Dim r, s, v As Long, vCEL, vSEL, vTMP As Variant
    With Selection
        vSEL = Application.Transpose(.Cells.Value)
        For s = LBound(vSEL) To UBound(vSEL)
            vCEL = Split(vSEL(s), Chr(44))
            For v = LBound(vCEL) To UBound(vCEL)
                r = r + 1
                .Cells(r, 1) = vCEL(v)
            Next v
        Next s
    End With
End Sub

Remember that the values you want to reorganize must be selected first. In this sample image, A1:A5 was selected and the macro was run. A copy of the original values was retained in C1:C5 for demonstration purposes.I'm not sure how the period (aka full stop) got in your sample data.

                 

Answer 2

Just in case you're interested in a formula-based solution, first go to Name Manager (Formulas tab) and define:

Range1 as an absolute reference to whatever your data range happens to be, e.g. =$A$1:$A$5

Arry1 as: =(LEN(Range1)>0)+LEN(Range1)-LEN(SUBSTITUTE(Range1,",",""))

Arry2 as: =MMULT(0+(ROW(Range1)>TRANSPOSE(ROW(Range1))),Arry1)

The array formula** in your first cell of choice is then:

=IF(ROWS($1:1)>SUM(Arry1),"",INDEX(TRIM(MID(SUBSTITUTE(INDEX(Range1,MATCH(TRUE,Arry2>=ROWS($1:1),0))&",",",",REPT(" ",999)),999*(ROW(INDIRECT("1:999"))-1)+1,999)),IF(ISERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry2)),ROWS($1:1),1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry2))))

Copy down as required.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).