Row to column data move base on row

[Andrew]

Hello,

I am looking for an easy way to take data listed down in rows and move
them to another spreadsheet as column values. the data looks
something like this:

ColA,ColB

Day, 1
Apples, 20
Orange, 10
Grapes, 5
Day, 2
Apples, 9
Orange, 7
Grapes, 3

and I want it to look like this :

Day Apples Oranges Grapes
1 20 10 5
2 9 7 3

The amount of days will change, but the data rows associated to each
day will always be 20. The end column name headers will also be
constant (i.e. Apples). I also need this to be a macro. I am using
Excel 2002.

Any ideas on how to do this?

Thanks in Advance!

 

[L. Howard Kittle]

Hi Andrew,

Select A1 to B4 Then Ctrl + c.
Select the cell where the word Day will be in your new table.
Edit > Paste Special > Transpose > OK.
Now select the numbers ONLY for day 2 and repeat.

HTH
Regards,
Howard

 

[L. Howard Kittle]

Whoops!! You probably wanted a VBA solution since you posted in
programming. Sorry.

Howard

 

[Andrew]

I am looking for a VB solution but im playing with the transpose
function now to see I can get that to work. Thanks for the tip!

 

[Andrew]

Whoops!! You probably wanted a VBA solution since you posted in
programming. Sorry.

Yeah, I am looking for a macro that can automate this process, but I
am having a really hard time writing this. Thanks for the help
though!

 

[L. Howard Kittle]

Hi Andrew,

Not sure if I have a solution or can come up with one. I assume the data is
not exactly as your example and is probably much more extensive.

But I would be glad to look at your worksheet if you are willing to send it
and take a shot at it.

Remove NOSPAM from my e-mail address.

Regards,
Howard

 

[Andrew]

Hello,

I found a solution that worked for me before the posts were viewable.
I really appreciate everyone's help on this. Its not the prettiest
code in the world, but it worked for me. I have taken my data out if
and replaced it with the 'Fruit Data'


Sub Fruit()

Dim R0 As Integer
Dim C0 As Integer
Dim C02 As Integer
Dim R1 As Integer
Dim C1 As Integer
C0 = 1
C02 = C0 + 1
R0 = 2
C1 = 1
R1 = 1
'Header = Cells(R0, C0).Value


Do Until IsEmpty(Worksheets("Import").Cells(R0, C0))

Worksheets("Import").Activate
Header = Worksheets("Import").Cells(R0, C0).Value


Select Case Header

Case "Days"
R1 = R1 + 1
Worksheets("Mohan").Cells(R1, 1) =
Worksheets("Import").Cells(R0, C02)
On Error Resume Next

Case "Apples"
Worksheets("Sheet2").Cells(R1, 2) =
Worksheets("Sheet1").Cells(R0, C02)
On Error Resume Next

Case "Oranges"
Worksheets("Sheet2").Cells(R1, 4) =
Worksheets("Sheet1").Cells(R0, C02)
On Error Resume Next

Case "Pears"
Worksheets("Sheet2").Cells(R1, 3) =
Worksheets("Sheet1").Cells(R0, C02)
On Error Resume Next

Case Else

End Select

R0 = R0 + 1

Loop


End Sub

 

[Kavi]

I'm also looking for the same, did you found anything, if
so, pls share here. Thanks.

Regards.

 

[Eddy]

Have you tried the "PasteSpecial" function in Excel?

Steps:
1) Select the entire range of your data and Copy
2) Select the cell you want to put the table
3) Select "Edit" / "Paste Special"
4) Check "Transpose" (Should be the check box right on top of the Okay
button)

My system is in Chinese. I do not know if Transpose is the right word.