Need VBA solution to find 1st and 2nd occurrences of non- zero, thenreturn column header value

[Aaron]

So here is how the data looks:

1 2 3 4 5 6 7 8 9
Bob 0 0 12 0 64 0 0 88 0
Jan 4 0 0 0 0 0 16 0 0
Steve 0 0 0 0 0 0 0 9 0
Tom 0 4 0 0 0 0 7 0 0

I need the output table to be:

Name 1stOccurrence 2ndOccurrence 3rd Occurrence
Bob 3 5 8
Jan 1 7
Steve 8
Tom 2 7

There is no pattern to the numbers that appear in the rows and the actual table has 120 columns and 120 rows.
Thanks very much, I've been trying for about 3 hours to get a series of formulas to work and its not coming together.

 

[Don Guillett]

So here is how the data looks:

1 2 3 4 5 6 7 8 9
Bob 0 0 12 0 64 0 0 88 0
Jan 4 0 0 0 0 0 16 0 0
Steve 0 0 0 0 0 0 0 9 0
Tom 0 4 0 0 0 0 7 0 0

I need the output table to be:

Name 1stOccurrence 2ndOccurrence 3rd Occurrence
Bob 3 5 8
Jan 1 7
Steve 8
Tom 2 7

There is no pattern to the numbers that appear in the rows and the actual table has 120 columns and 120 rows.
Thanks very much, I've been trying for about 3 hours to get a series of formulas to work and its not coming together.

Sub getoccur()
Dim i As Long
Dim j As Long
Dim dc As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
dc = 11
For j = 2 To Cells(i, Columns.Count).End(xlToLeft).Column
If Cells(i, j) > 0 Then
Cells(i, dc) = j - 1
dc = dc + 1
End If
Next j
Next i
End Sub

 

[Aaron]

Thanks very much....I went with Don's VBA since I was worried that an array might not scale up as well.

Is there a way to return the value in the top row instead of the column count? If not, I will try to do a lookup or something to get that value.

Thanks again!

 

[Aaron]

The array formula returns the value in the top row.

Array works great Ron..Thanks! I always thought a bunch of arrays would slow the program down but that is not the case.

 

[Don Guillett]

Thanks very much....I went with Don's VBA since I was worried that an array might not scale up as well.

Is there a way to return the value in the top row instead of the column count? If not, I will try to do a lookup or something to get that value.

Thanks again!

Since you had numbers in the top row in your example, I assumed you wanted to start with row 2 instead of row 1. Change the for I code