check a string of numbers for duplicates

[Jaffa_ca]

I'm trying to create a formula to check to see if a row of 4 cells with numbers in each cell is every duplicated. and then either highlight it or show the row number to where the duplicate is

example:
row a 1,2,3,4
row b 2,3,4,5
row c 3,4,5,6
row d 1,2,3,4

in this example row d would be the duplicate
i'm was trying to use vlookup without any success

would anyone here be able to help with this type of formula??
thanks in advance
Jeff

 

[James006]

Hello,

You have obviously posted an illustration of your question ...

How large is you actual file ...???

 

[admiral100]

Hi,

Try this:-

F1 =IF(SUMPRODUCT(--($A$1:$A$4=A4),--($B$1:$B$4=B4),--($C$1:$C$4=C4),--($D$1:$D$4=D4)*1)>1,"Duplicates","No duplicates")

	A	B	C	D	E	F
1	1	2	3	4		Duplicates
2	2	3	4	5		No duplicates
3	3	4	5	6		No duplicates
4	1	2	3	4		Duplicates

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

 

[James006]

Hello again,
@admiral100 ...

My assumption is that for cell F1 ... you meant :

=IF(SUMPRODUCT(--($A$1:$A$4=A1),--($B$1:$B$4=B1),--($C$1:$C$4=C1),--($D$1:$D$4=D1))>1,"Duplicate","")

 

[admiral100]

You're right

 

[Rick Rothstein]

You could also use this formula (I believe COUNTIFS is supposed to be more efficient than SUMPRODUCT)...

=IF(COUNTIFS(A$1:A$4,A1,B$1:B$4,B1,C$1:C$4,C1,D$1:D$4,D1)>1,"Duplicates","")

 

[James006]

Still wondering how large is the actual database ... both in terms of Rows and in terms of Columns .. ???

 

[Jaffa_ca]

Hello James

the database is 4 columns and 562 rows the columns may expand

it our database for our member numbers for our club, as people join the club the datas could grow larger in columns

 

[James006]

Hello,

Your database is much smaller than what I was expecting ...

Basically ... both proposals of a Formula ... shown above should work out fine ...

 

[Jaffa_ca]

I've used rick's formula, it works great!! thanks for all the help