Sort of a transpose. 1

[micang]

Excel 2003:

Hi All,

If I have data like this:

Jan Feb Mar Apr
5 35 43 4
2 53 2 9
1 35 5 9
2 5 3 99
2 3 6 8
5 53 9
2 5 99
1 35 1

(the data is not constant)


Is there a way to getting it to look like:

5 Jan
2 Jan
1 Jan
2 Jan
2 Jan
35 Feb
53 Feb
35 Feb
5 Feb
3 Feb
5 Feb
2 Feb
1 Feb
43 Mar
2 Mar
5 Mar
3 Mar
6 Mar
53 Mar
5 Mar
35 Mar
4 Apr
9 Apr
9 Apr
99 Apr
8 Apr
9 Apr
99 Apr
1 Apr

MAny thanks.

Michael

 

[globalbear]

place your table in cell A1

then run this code

I think it should do the trick

Sub new_table()

row_copy_to = 1 'row to copy to, start on row no one
col_copy_to = 10 'column to copy to, for example col ten
header_row = 1 'header row
row_copy_from = 2 'row to copy from, start on the second line

For col_copy_from = 1 To 4 'column to copy from, in your case you have four cols

Do While Cells(row_copy_from, col_copy_from) <> ""
Cells(row_copy_to, col_copy_to) = Cells(row_copy_from, col_copy_from)
Cells(row_copy_to, col_copy_to + 1) = Cells(header_row, col_copy_from)
row_copy_from = row_copy_from + 1
row_copy_to = row_copy_to + 1
Loop

Next col_copy_from

End Sub

 

[micang]

Tahnks for the input globalbear.

If I run it against the sample above, I get:

5 Jan
2 Jan
1 Jan
2 Jan
2 Jan
2 Jan
2 Feb
1 Feb

I am new to VBA so I have no idea where to take it from here.

Many thanks

Michael

 

[globalbear]

Well January seems to work but then February etc do not.

Paste your table in a fresh new spreadsheet. My code assumes that all cells that don't have any numbers really are empty (meaning no spaces or such). That's what the line
Do While Cells(row_copy_from, col_copy_from) <> ""
does. If the cell is not really empty it will not work.

It works fine for me.

 

[micang]

I don't understand it, for me it only does Jan, no other columns.

I have entered data into a completely new fresh sheet.

Michael

 

[globalbear]

Ooops, sorry...

I realize that I put the
line row_copy_from = 2
on the wrong place...
It should be inside the for/next-loop

Try this instead!

Sub new_table()

row_copy_to = 1 'row to copy to, start on row no one
col_copy_to = 10 'column to copy to, for example col ten
header_row = 1 'header row

For col_copy_from = 1 To 4 'column to copy from, in your case you have four cols

row_copy_from = 2 'row to copy from, start on the second line

Do While Cells(row_copy_from, col_copy_from) <> ""
Cells(row_copy_to, col_copy_to) = Cells(row_copy_from, col_copy_from)
Cells(row_copy_to, col_copy_to + 1) = Cells(header_row, col_copy_from)
row_copy_from = row_copy_from + 1
row_copy_to = row_copy_to + 1
Loop

Next col_copy_from

End Sub

 

[micang]

fantastic globalbear, works fine now.

Can it be amended to run also if the column number change?

i.e. instead of 4 do from A1 to end of data?

It works great though, thansk you so much, a star for you.

Michael

 

[globalbear]

Yes of course you could. Try changing the line
For col_copy_from = 1 To 4
to the number of cols you prefer.

A better way of course would be to change 4 to a variable such as
For col_copy_from = 1 To last_col

But then you have to figure out how to give last_col the correct value - that one I leave you to find out by yourself...

One way could be to use an inputbox such as
last_col = InputBox("Last column number?", "Enter value")

But that would leave you to manually enter the column number. You could let Excel find it for you but that's for you to find out...

 

[SkipVought]

Hi,

Also, FYI...

NORMALIZE Your Table using the PivotTable Wizard faq68-5287

Skip,
[sub]
[red][/red]
[/sub]

 

[micang]

Thanks globalbear, i will look into that.

Thanks so much, this works great.

Michael