Traspose columns into rows, copying data from the original row in the new ones

[alvaro_v_b]

Hello Everybody!

I have a difficult task ahead and I am not sure if what I want to do is possible to be done in an authomatic way.
I have a data base with several fields/columns: A, B, C, D. In the last one (D), each field contains several words separated by a delimiter.
What I want is, for each row to generate as many rows as delimited words in column D, filling columns A, B and C with the same content of the original row.
For instance, I want to automaticly transform something like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]ShapeKey
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Class[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65"] Subclass
[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Products[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1104
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]

Parade floats;Motor cars;Mobile laboratories;Motor graders ;Trucks for liquid manure

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]65
[/TD]
[TD]12
[/TD]
[TD]02
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Trolleys;Wheel carriers ;Dollies
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Into something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ShapeKey
[/TD]
[TD]Class
[/TD]
[TD]Subclass
[/TD]
[TD]Products
[/TD]
[/TR]
[TR]
[TD]1104
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]Parade floats
[/TD]
[/TR]
[TR]
[TD]1104
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]Motor cars
[/TD]
[/TR]
[TR]
[TD]1104
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]Mobile laboratories
[/TD]
[/TR]
[TR]
[TD]1104
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]Motor graders
[/TD]
[/TR]
[TR]
[TD]1104
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]Trucks for liquid manure
[/TD]
[/TR]
[TR]
[TD]65
[/TD]
[TD]12
[/TD]
[TD]02
[/TD]
[TD]Trolleys
[/TD]
[/TR]
[TR]
[TD]65
[/TD]
[TD]12
[/TD]
[TD]02
[/TD]
[TD]Wheel carriers
[/TD]
[/TR]
[TR]
[TD]65
[/TD]
[TD]12
[/TD]
[TD]02
[/TD]
[TD]Dollies
[/TD]
[/TR]
</tbody>[/TABLE]

(In the real case I have many more rows)
Does anybody know if this is possible in Excel?

THANK YOU SO MUCH IN ADVANCE FOR YOUR HELP!!

Alvaro

 

[mumps]

Re: Traspose columns intro rows, copying data from the original row in the new ones

This macro will place you new data in Shet2. Change the sheet names in the macro to suit your needs.

Sub SplitCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim Product As Range
    Dim prodArray As Variant
    Dim i As Long
    Sheets("Sheet1").Rows(1).Copy Sheets("Sheet2").Cells(1, 1)
    For Each Product In Sheets("Sheet1").Range("D2:D" & LastRow)
        prodArray = Split(Product, ";")
        For i = LBound(prodArray) To UBound(prodArray)
            Sheets("Sheet1").Range("A" & Product.Row & ":C" & Product.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = prodArray(i)
        Next i
    Next Product
    Application.ScreenUpdating = True
End Sub

 

[alvaro_v_b]

Re: Traspose columns intro rows, copying data from the original row in the new ones

Wow mumps, this looks great!!
I have just tried it and it works perfectly, CONGRATULATIONS AND THANK YOU SO MUCH!

 

[mumps]

Re: Traspose columns intro rows, copying data from the original row in the new ones

You are very welcome.