Hi,
I would like to know how to achieve the same task using the VBA tool.
Ideally I would like to get the Item Number (located in the first column) of
the array Im duplicating in a message box.

Any help would be greatly appreciated!

--
Edwin


"Herbert Seidenberg" wrote:

Excel limits the number of terms in SUMPRODUCT() to 30,
so you cannot expand my formulas to 96 columns.
If your data is somewhat random, then even if you have just 10 columns,
the chances of rows repeating are astronomical.
Even so, it can be done with a huge increase in complexity and
spreadsheet space.
The hidden named arrays have to be turned into visible, dynamic arrays.
ArrV is a 96x100 array with this array formula:
=--(INDEX(ArrN,LenArr,COLUMN(ArrN))=INDEX(ArrN,,COLUM N(ArrN)))
ArrE refers to: =ROW(INDEX(ArrV,1,):INDEX(ArrV,COLUMNS(ArrV),))^0
ArrM is a 1x100 array with this array formula:
=MMULT(ArrV,ArrE)
and the flag (1) formula is:
=COUNTIF(ArrM,96)