Excel Formula Inquiry - Numbers to determine duplicate

[Jerome_RA]

Hello, Can you help me with this..

So i have 5 numbers ; From A1 to E1 ; and going down and so on.
I want to get a formula to determine if that number from the rows has been used ; all 5 of them in any order.

so let say 17,16,15,11 and 10 from A1 to E1 has duplicates from another rows in any order, a message on probably F1 will say that it has duplicate

A	B	C	D	E
10	11	15	16	17
1	16	56	85	44

 

[awoohaw]

Do you want all five in a row or all five anywhere in the range your searching?

 

[felixstraube]

How about:

Book1
	A	B	C	D	E	F
1	1	2	3	4	5	Has duplicates!!!
2	6	4	8	9	10	Has duplicates!!!
3	2	12	13	14	15	Has duplicates!!!
4	16	3	18	19	20	Has duplicates!!!
5	21	22	23	24	25	All ok
6	26	27	28	29	30	All ok
7	31	32	33	34	35	All ok
Sheet1

Cell Formulas
Range	Formula
F1:F7	F1	=LET(d, $A$1:$E$100, r, A1:E1, IF(MAX(BYCOL(r, LAMBDA(x, SUM((x=d)*(d<>"")))))>1, "Has duplicates!!!", "All ok") )

 

[Jerome_RA]

Do you want all five in a row or all five anywhere in the range your searching?

all five in a row ; so for instance, A1 to E1 has a random number but A4 to E4 has the same numbers but not the same order as A1 to E1 ; ; it will still say duplicate

 

[Jerome_RA]

Kind of like this

A	B	C	D	E	F
1​	2​	3​	4​	5​	Duplicate
5​	3​	2​	4​	1​	Duplicate
7​	8​	9​	10​	11​	OK
7​	8​	9​	1​	2​	OK

 

[awoohaw]

What version of excel are you using? (By the way, please update your profile so your version shows up on your button and the next person doesn't need to ask). Solutions may not be available in some versions of excel, or very complicated to accomplish.

 

[awoohaw]

This solution uses BYROW and LAMBDA functions, if you get errors it may be because of your version:

Book1
	A	B	C	D	E	F	G
1	1	2	3	4	5	Duplicate	Duplicate
2	5	3	2	4	1	Duplicate	Duplicate
3	7	8	9	10	11	OK	No Duplicate
4	7	8	9	1	2	OK	No Duplicate
Sheet1

Cell Formulas
Range	Formula
G1:G4	G1	=IF(SUM(--(BYROW((A1=$A$1:$E$4)+(B1=$A$1:$E$4)+(C1=$A$1:$E$4)+(D1=$A$1:$E$4)+(E1=$A$1:$E$4),LAMBDA(r,SUM(r)))=5))>1,"Duplicate","No Duplicate")

 

[Jerome_RA]

What version of excel are you using? (By the way, please update your profile so your version shows up on your button and the next person doesn't need to ask). Solutions may not be available in some versions of excel, or very complicated to accomplish.

Thanks ; I already updated my Profile. I havent done so in the past. My apologies.. Its Excel 2013 so the LAMDA function did not work for my version : (

 

[awoohaw]

Thanks @Jerome_RA , yeah, your version of excel has me stumped. This is what I've come up with and it is by no means elegant.
What is worse you will need need huge help areas to avoid #SPILL errors.

I am very much hoping that a forum member that is very knowledgeable about #VALUE errors with array formulas will chime in. As, I am pretty sure that this can be done. I think the problem is that some functions only work with ranges and not arrays. So when an array has properly spilled it is now also in a range.

As you can see, I have two formulas (B12 and B19) that are based on array formulas that are spilled into ranges just above those formulas.
But, when I have used the same formula but having the array referenced I can't even commit the formula to the cell and get the described error.

NOTE: I hard coded the "2" in the formulas as I was stuck, but normally I would have used the row function to get a dynamic number in that part of the formulas.

Book1
	A	B	C	D	E	F	G
1	1	1.00	2.00	3.00	4.00	5.00	Duplicate
2	2	2.00	3.00	4.00	5.00	1.00	Duplicate
3	3	7.00	8.00	9.00	10.00	11.00	OK
4	4	7.00	8.00	9.00	1.00	2.00	OK
5
6
7		0	0	0	0	0
8		2	2	2	2	2
9		0	0	0	0	0
10		0	0	0	4	4
11
12		5		=COUNTIF(B7#,2)
13
14		0	0	0	0	0
15		2	2	2	2	2
16		0	0	0	0	0
17		0	0	0	4	4
18
19		5		=COUNTIF(B14#,2)
Sheet1

Cell Formulas
Range	Formula
B7:F10	B7	=((((((B1=($B$1:$F$4))*($A$1:$A$4)) +((C1=($B$1:$F$4))*(($A$1:$A$4))) +((D1=($B$1:$F$4))*(($A$1:$A$4)))) +((E1=($B$1:$F$4))*(($A$1:$A$4))) +((F1=($B$1:$F$4))*(($A$1:$A$4))))<>ROW(A1)) * ((((B1=($B$1:$F$4))*(($A$1:$A$4))) +((C1=($B$1:$F$4))*(($A$1:$A$4))) +((D1=($B$1:$F$4))*(($A$1:$A$4)))) +((E1=($B$1:$F$4))*(($A$1:$A$4))) +((F1=($B$1:$F$4))*(($A$1:$A$4)))))
B12,B19	B12	=COUNTIF(B7#,2)
D12,D19	D12	=FORMULATEXT(B12)
B14:F17	B14	=( (((B1=($B$1:$F$4))*ROW($A$1:$A$4)) +((C1=($B$1:$F$4))*ROW($A$1:$A$4)) +((D1=($B$1:$F$4))*ROW($A$1:$A$4)) +((E1=($B$1:$F$4))*ROW($A$1:$A$4)) +((F1=($B$1:$F$4))*ROW($A$1:$A$4))) <>ROW(A1)) * (((B1=($B$1:$F$4))*ROW($A$1:$A$4)) +((C1=($B$1:$F$4))*ROW($A$1:$A$4)) +((D1=($B$1:$F$4))*ROW($A$1:$A$4)) +((E1=($B$1:$F$4))*ROW($A$1:$A$4)) +((F1=($B$1:$F$4))*ROW($A$1:$A$4)))
Dynamic array formulas.

This is the pop up I get when I enter the formula in cell H1.