match of numbers

[RANNYS]

Hello all.

I have a row with 20 random numbers.
I want to know whether there is at least one match between two numbers along the row.

The indication can be with a true/false or a 1/0, using a formula at the end of the row.

I'd appreciate any assistance.
Thanx,
Ranny

 

[Aladin Akyurek]

Try:

=(MAX(FREQUENCY(A2:T2,A2:T2))>1)+0

 

[RANNYS]

Thank you. it works.
I have no experience with the frequeny formula.
Could you please explain the way it works?

 

[Aladin Akyurek]

Thank you. it works.
I have no experience with the frequeny formula.
Could you please explain the way it works?

FREQUENCY(NumericData,Bins)

sorts the numeric data into bins it is given, such that it creates an occurrence count for each bin.

Example:

FREQURENCY({1,2,3},{1,3})

==>

{1;2;0}

Entry 1 of the first term maps onto bin 1 (Bin 1 occurrence count is 1). The entries 2 and 3 fits bin 3, that is, bin 3 gets every entry that is > 1 (bin 1) and less than or equal to 3 (bin 3). Hence bin 3 occurrence count is 2. Note that the function finds no entry > bin 3, so the rest bin occurrence is 0.

FREQUENCY(A2:T2,A2:T2)

uses as bins the entries it id fed with. Any bin count greater than or equal to 1 would satisfy the condition of having a duplicate (a match). Hence the MAX test > 1.

Hope this helps.

 

[circledchicken]

Hi Aladin,

Can you please help me understand why this setup doesn't require CTRL-SHIFT-ENTER?
While a similar COUNTIF version for example - =...MAX(COUNTIF(A2:T2, A2:T2))... would require CSE?

Is it just a nice property of the FREQUENCY function and a case of that being how the function is built to behave?
Or perhaps there is something more underlying it that I don't understand?

 

[Aladin Akyurek]

Hi Aladin,

Can you please help me understand why this setup doesn't require CTRL-SHIFT-ENTER?
While a similar COUNTIF version for example - =...MAX(COUNTIF(A2:T2, A2:T2))... would require CSE?

Is it just a nice property of the FREQUENCY function and a case of that being how the function is built to behave?
Or perhaps there is something more underlying it that I don't understand?

FREQUENCY like LOOKUP and SUMPRODUCT is designed as an array-processing function, so control+shift+enter confirmation is not required. By the way, it is also faster than CountIf(s).

 

[circledchicken]

Ok, thanks for your quick reply!

 

[RANNYS]

Yes, this really helps.
Thank you so much.

 

[Aladin Akyurek]

Yes, this really helps.
Thank you so much.

You are welcome.