Hi Garys student, I have a similar array of data except that the column
headings are Country names, not numbers. Your macro is working, but it is
returning numbers, not the country names like below. How can I address this?
Thanks!



"Gary''s Student" wrote:

Assume that the original data is in sheet s1 and the final data will be in
sheet s2. If we start with:

Name 1 2 3 4 5 6 7
Joe 20 30 40
Mike 9 8 7 6 5 4 3
Jim 44 55 66 77 88
Trevor 34

and run this macro:

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

We will find on s2:

Joe 1 20
Joe 2 30
Joe 3 40
Mike 1 9
Mike 2 8
Mike 3 7
Mike 4 6
Mike 5 5
Mike 6 4
Mike 7 3
Jim 1 44
Jim 2 55
Jim 3 66
Jim 4 77
Jim 5 88
Trevor 1 34

--
Gary''s Student - gsnu200787


"Ray S." wrote:

I have a large array that has names as row headings and week dates as column
headings with hours as the data. It looks like a crosstab query would in
Access. What I want to do is convert it to a list where the names repeat in
one column, the week dates for each name repeat in the second column and the
hourly data fills the third column. Example:

name 1 2 3
nancy 40 38 39

I want

name week hours
nancy 1 40
nancy 2 38
nancy 3 39

Is there an easy way of doing this? My array is really quite large and has
many names.