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.

 

[Ron Rosenfeld]

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.

Your title looks for a VBA solution, but your text is about formulas.

For a formula solution, assuming your data is in the range A1:J5, you can try:

A10: =A2
Fill down to A13

B9: 1stOccurrence C9: ...

B10:
This formula must be **array-entered**:

=IFERROR(INDEX($1:$1,1,SMALL(ISNUMBER(1/$A2:$J2)*COLUMN($A2:$J2),
COLUMNS($A2:$J2)-COUNTIF($A2:$J2,">0")+COLUMNS($A:A))),"")

Select a fill across to D10. Then select row and fill down

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

To expand the example to encompass 120 columns, you'll probably want to place it on another sheet, but use the same kind of reference address modes.

Something like (assuming your data is in Sheet1):

=IFERROR(INDEX(Sheet1!$1:$1,1,SMALL(ISNUMBER(1/Sheet1!$A2:$DP2)*COLUMN(Sheet1!$A2:$DP2),
COLUMNS(Sheet1!$A2:$DP2)-COUNTIF(Sheet1!$A2:$DP2,">0")+COLUMNS($A:A))),"")

If you really need a VBA solution, post back.

 

[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!

 

[Ron Rosenfeld]

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!

The array formula returns the value in the top row.

 

[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.

 

[Ron Rosenfeld]

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

Glad to help. Thanks for the feedback.

An array formula can be slow under various circumstances, including defining a range that is much larger than needed. But that should not be the case with a 120x120 matrix, and something as straightforward as your requirements. But for a VBA routine to be faster, it would need to be "artfully crafted"

 

[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