Finding Duplicate Arrays in a table
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)
|