change array to simple columns

[Ray S.]

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.

 

[Ray S.]

OK, I was reading about arrays and there may be another way to do what I want
to do. My problem is that I have two copies of my very large array. I want to
compare the two arrays to see where they differ, if at all. Say, in my
mini-example below, if I had two copies of the array and one differed from
the other, let's say the second copy had this instead of the one I already
noted:

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

Is there some function or procedure whereby I could compare the two arrays
and determine that one copy has 40 hours for nancy in the third week while
the other has 40 hours for her in the same week?

Any suggestions?

 

[Gary''s Student]

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

 

[Ray S.]

Your macro returns a subscript out of range error.

 

[Ray S.]

I also notice that a line in your code is missing a then statement. It just
jumps to an Else.

 

[Ray S.]

OK, Gary's Student, I figured out what was making the code not work for me,
and it worked great. Thanks. Did you see my second post? I was reading about
arrays and array functions. Do you have any suggestions about how I may be
able to do what I wanted? That is, to compare two large arrays. I want to see
where and if they differ in the number of hours for each person's week.

 

[Gary''s Student]

Now that we got the first program to work, I will take a look at the second
request.


Check back later.

 

[seanoniallain]

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!