Function to compare values

[Collcat]

HI,
I need help with a function that compares data.
I want to compare 11 values and if any of the values are the same, th
function should indicate that 1 or more values are the same. Like a fla
that something is wrong. If they are all different the function shol
indicate that.

Thank You in advance

// Coolca

 

[Frank Kabel]

Hi
try the following formula (returns TRUE if duplicates exist):
=SUMPRODUCT(--(COUNTIF($B$1:$B$10,$B$1:$B$10)>1))

 

[Harlan Grove]

try the following formula (returns TRUE if duplicates exist):
=SUMPRODUCT(--(COUNTIF($B$1:$B$10,$B$1:$B$10)>1))

The formula as written doesn't return TRUE/FALSE. For that you'd need

=SUMPRODUCT(--(COUNTIF($B$1:$B$10,$B$1:$B$10)>1))>0

But the OP may prefer the shorter array formula

=OR(COUNTIF($B$1:$B$10,$B$1:$B$10)>1)

 

[Frank Kabel]

The formula as written doesn't return TRUE/FALSE. For that you'd need

=SUMPRODUCT(--(COUNTIF($B$1:$B$10,$B$1:$B$10)>1))>0

Hi Harlan
thanks for the correction. Missed the last bit

Frank

 

[Daniel.M]

Hi Harlan,

But the OP may prefer the shorter array formula

=OR(COUNTIF($B$1:$B$10,$B$1:$B$10)>1)

Also this one, which does not have to be array entered (although an 'implicit'
array formula):

=OR(FREQUENCY(B1:B10,B1:B10)>1)

I think it's also faster for big ranges.

Cause array COUNTIF(n,m) is in order of n*m
FREQUENCY(n,m) is in order of n*log(m,2) + 2*m*log(m,2)
' one sort of m to 'place' the bins in ascending order, then n * Matches(n,m,1),
then one re-sort of counts_results to initial order of the bins

In this instance, n=m, and the preceedings could be abbreviated to:
Countif ==> O(n2)
Frequency ==> O(3n*log(n,2))

If that's true ('cause my frequency order analysis is purely conjecture as I
don't know the exact algos used), the latter will be faster for any significant
number of cells (let's say 10K).

I've written a small program (see below) and for m around 10K cells, result
align with the above statements.

Regards,

Daniel M.


Declare Function GetTickCount Lib "kernel32" () As Long

Const NITER = 100

Sub test()

Dim time1 As Long, time2 As Long
Dim i As Long, j As Boolean

time1 = GetTickCount()
For i = 1 To NITER
j = Evaluate("OR(COUNTIF(m,m)>1)")
Next i
time2 = GetTickCount()

Debug.Print "Countif = " & time2 - time1

time1 = GetTickCount()
For i = 1 To NITER
j = Evaluate("OR(FREQUENCY(m,m)>1)")
Next i
time2 = GetTickCount()

Debug.Print "FREQUENCY = " & time2 - time1

End Sub

 

[Harlan Grove]

Also this one, which does not have to be array entered (although an
'implicit' array formula):

=OR(FREQUENCY(B1:B10,B1:B10)>1)

....

OP used the term 'values', which could mean numbers or anything. FREQUENCY
only works with numbers. You could use

INDEX(FREQUENCY(MATCH(B1:B10,B1:B10,0),{1}),2)>0

to deal with text or numbers, but the MATCH(x,x,0) makes it > O(N^2), so
nothing gained vs COUNTIF(x,x).

 

[Daniel.M]

OP used the term 'values', which could mean numbers or anything. FREQUENCY
only works with numbers.

You're right.
I only used numbers.

Thanks,

Daniel M.