Find first occurence of a number in an array 7 cols wide
 

In a lotto analysis spread sheet, I want to find row of the first occurence
of each number (1 to 49) in a 2 dimendional array 7 columns wide--essentially
to find how many spins since the last draw of the number. Can this be done
using functions only e.g. MATCH or do I need to learn VBA quickly?

Find first occurence of a number in an array 7 cols wide
 

*Maybe* something like this...

Numbers in the range A1:G100

Numbers 1:49 in the range I1:I49

Enter this array formula** in J1 and copy down to J49:

=MIN(IF(A$1:G$100=I1,ROW(A$1:G$100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Ricardo-SA" wrote in message
...
In a lotto analysis spread sheet, I want to find row of the first
occurence
of each number (1 to 49) in a 2 dimendional array 7 columns
wide--essentially
to find how many spins since the last draw of the number. Can this be done
using functions only e.g. MATCH or do I need to learn VBA quickly?

Find first occurence of a number in an array 7 cols wide
 

That is brilliant -- just what I needed -- thank you.

Find first occurence of a number in an array 7 cols wide
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ricardo-SA" wrote in message
...
That is brilliant -- just what I needed -- thank you.