Change a crostab report to a simple columnar format

[seanoniallain]

I have data in a crosstab type report in excel which I want to convert to a
columnar report. The macro below performs the conversion but does not list
the column headings as countries, but as numbers 1,2,3 etc. Is it possible to
amend the macro to achieve what I want? My data is extensive with 50+ columns.

I have this:

France Australia Austria
Data 1 567,648 15,673 897,197
Data 2 25 555 98,745
Data 3 17 6,651 7,888
Data 4 88 8,162 2,893

And I want this:
Data 1 France 567,648
Data 1 Australia 15,673
Data 1 Austria 897,197
Data 2 France 25
Data 2 Australia 555
Data 2 Austria 98,745
Data 3 France 17
Data 3 Australia 6,651
Data 3 Austria 7,888
Data 4 France 88
Data 4 Australia 8,162
Data 4 Austria 2,893


This is the current macro but it is returning column numbers instead of the
country names:
Sub rays()
Set s1 = Sheets("s1")
Set s2 = Sheets("s2")
last_row = s1.Cells(Rows.Count, "A").End(xlUp).Row
new_row = 2
For i = 2 To last_row
name_is = s1.Cells(i, 1).Value
s2.Cells(new_row, 1).Value = name_is
For j = 2 To 53
If IsEmpty(s1.Cells(i, j)) Then
Else
s2.Cells(new_row, 1).Value = name_is
s2.Cells(new_row, 2).Value = j - 1
s2.Cells(new_row, 3).Value = Cells(i, j).Value
new_row = new_row + 1
End If
Next
Next
End Sub

 

[Roger Govier]

hi Sean

You are using i (2) for your cell name
Change it to 1

For i = 2 To last_row
name_is = s1.Cells(1, 1).Value

 

[Seanoniallain]

Thanks Roger. I've made the change but I guess I'm doing something silly
because the macro is returning gibberish. Could you update the macro I pasted
in so that I'm sure I'm making the correct change?

Thanks!

John

 

[Seanoniallain]

Thanks Roger. Could you incorporate the change on the macro I pasted in as
when I make the changes it fails to return correct results. Not sure I'm
making the change exactly as I should.

Thanks,

John

 

[Roger Govier]

Hi John

not what I told you before at all.

Try
Sub rays()
Set s1 = Sheets("s1")
Set s2 = Sheets("s2")
last_row = s1.Cells(Rows.Count, "A").End(xlUp).Row
new_row = 2
For i = 2 To last_row
name_is = s1.Cells(i, 1).Value
s2.Cells(new_row, 1).Value = name_is
For j = 2 To 53
If IsEmpty(s1.Cells(i, j)) Then
Else
s2.Cells(new_row, 1).Value = name_is
s2.Cells(new_row, 2).Value = s1.Cells(1, j).Value
s2.Cells(new_row, 3).Value = s1.Cells(i, j).Value

new_row = new_row + 1
End If

Next

Next
End Sub