Hi,

I have a table with several rows and columns of information. I will
continue to add new information new rows and I want to make sure that I never
have duplicate a row.

So here is a simple example:

A B C
1 i 1 k

2 r 3 n

3 a 2 f

4 a 1 f

5
I want to be able to add another row of information and if the array in that
row from columns a to c matches an array I already have I want to be notified
and it would also be nice to know which array I am duplicating.

Can anyone help?

Thanks,
John

This assumes you are familiar with dynamically named ranges.
Name the maximum size of your table.
Insert Name Define Names in Workbook ArrT
Refers to: =$A$1:$C$100 for a 3x100 array
Also define the following names
LenArr
Refers to: =COUNTA(INDEX(ArrT,,1))
ArrN
Refers to: =INDEX(ArrT,1,):INDEX(ArrT,LenArr,)
Fac1
Refers to: =--(INDEX(ArrN,LenArr,1)=INDEX(ArrN,,1))
Fac2
Refers to: =--(INDEX(ArrN,LenArr,2)=INDEX(ArrN,,2))
Fac3
Refers to: =--(INDEX(ArrN,LenArr,3)=INDEX(ArrN,,3))
Continue like this if you have more than 3 columns
If this formula is greater than 1, the last row entry is a duplicate:
=SUMPRODUCT(Fac1*Fac2*Fac3)
You can conditionally format this cell to draw your attention.
This formula will give you the location of the duplicate row:
=SUMPRODUCT(Fac1*Fac2*Fac3*ROW(ArrN))-LenArr

Wow I setup up a simple case and this is going to work great!!! Thank you so
much. I have one question though I have a total of 96 columns so is there a
way to accomodate that or should I define 96 Fac#s? I'm fine with doing that
if I have to, because I'm glad you gave me this solution, but just thought I
would ask.

Thank You,
John

"Herbert Seidenberg" wrote:

This assumes you are familiar with dynamically named ranges.
Name the maximum size of your table.
Insert Name Define Names in Workbook ArrT
Refers to: =$A$1:$C$100 for a 3x100 array
Also define the following names
LenArr
Refers to: =COUNTA(INDEX(ArrT,,1))
ArrN
Refers to: =INDEX(ArrT,1,):INDEX(ArrT,LenArr,)
Fac1
Refers to: =--(INDEX(ArrN,LenArr,1)=INDEX(ArrN,,1))
Fac2
Refers to: =--(INDEX(ArrN,LenArr,2)=INDEX(ArrN,,2))
Fac3
Refers to: =--(INDEX(ArrN,LenArr,3)=INDEX(ArrN,,3))
Continue like this if you have more than 3 columns
If this formula is greater than 1, the last row entry is a duplicate:
=SUMPRODUCT(Fac1*Fac2*Fac3)
You can conditionally format this cell to draw your attention.
This formula will give you the location of the duplicate row:
=SUMPRODUCT(Fac1*Fac2*Fac3*ROW(ArrN))-LenArr

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)

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)