Need Help Converting Tabular Data to Columns

Thanks to all for their help on this forum. Great job !!

I was wondering if someone coule help me. I would wonder is there an easy way to reorganize tabular data into individual columns ie. a macro ...


ORIGINAL DATA
--------------
Columns (C to AF): Car Models
Rows (3 to 62): Car Colors

B C D E F ..... AF
2 Audi Merceds Honda Toyota Chevrolet
3 Red 10 11 12 41 23
4 Pink 2 2 0 5 3
.
.
.
6 Green 0 34 5 21 11



NEW DATA
--------
C D E F ..... AF AG AH AI AJ .......
Red Audi Red Mercedes Red Honda Red Toyota Red Chevrolet Pink Audi Pink Merceds Pink Honda Pink Toyota
10 11 12 41 23 2 2 0 5 .......

I've attached a txt file if the formatting of this message gets messed up.

Thanks.
-raj

I am considering that Columns (C3 to AF): Car Models,Rows (3 to 62): Car Colors

You need to have Sheet2 sheet before executing this macro.

The result will be copied to Sheet2.

Try this macro

Sub macro()
Sheets("Sheet1").Select
Dim i As Integer
i = 0
Dim k As Variant
k = 3
Dim offset_x, offset_y As Variant
offset_x = 0
offset_x1 = 0
offset_y = 0
Dim counter As Variant
While i = 0
If (Range("b" & k).Value = "") Then
i = 1
Else
Dim k1 As Variant
Range("c2").Select
offset_x1 = 0
counter = 0
While ActiveCell.Value <> ""
temp = ActiveCell.Value
color_val = Range("b" & k).Value
Sheets("Sheet2").Select
Range("c2").Offset(0, offset_x).Select
ActiveCell.Value = color_val & " " & temp
offset_x = offset_x + 1
offset_x1 = offset_x1 + 1
Sheets("Sheet1").Select
Range("c2").Offset(0, offset_x1).Select
counter = counter + 1
Wend
Range("c" & k).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveCell.Offset(1, (-counter) + 1).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
k = k + 1
offset_y = offset_y + 1
End If
Wend
End Sub