VBA Verify that array has unique values

[Hammerjoe]

Hello,

I was wondering if there is a simple method to check if an array (or it could be a range in a sheet) has unique values?

The idea is that I need to use an 26 array (or a range from B1 to B26) and each cell has to have an unique number from 1 to 26.

I want to find the right combination of digits in the array that will solve a math equation.

I want to cycle each cell till I find the match

Is there a simple routine to do that?

 

[JoeMo]

Hello,

I was wondering if there is a simple method to check if an array (or it could be a range in a sheet) has unique values?

The idea is that I need to use an 26 array (or a range from B1 to B26) and each cell has to have an unique number from 1 to 26.

I want to find the right combination of digits in the array that will solve a math equation.

I want to cycle each cell till I find the match

Is there a simple routine to do that?

This formula will check if the 26 numbers (or text entries) are unique..

=IF(SUMPRODUCT((COUNTIF(B1:B26,B1:B26)))=26,"Unique","Not Unique")

 

[kalak]

If your values in the array are all positive integers (1 to 26) you can try this bit of VBA

Sub uniques_in_array()
Dim b(1 To 26) As Boolean, Arr, a, c
Arr = Range("B1:B26")
For Each a In Arr
    If Not b(a) Then b(a) = True: c = c + 1
Next a
MsgBox c & " unique integers in array"
End Sub

 

[Hammerjoe]

That should work nicely and its simple.

Would it be possible to expand this code to permute with the values for the next unique sequence?
example, b1=1 b2=2 b3=3 b4=4.
next sequence would then be b1=1 b2=2 b3=4 b4=3
Basically im looking for a loop that will try find every singe unique key sequence??

 

[kalak]

That should work nicely and its simple.

Would it be possible to expand this code to permute with the values for the next unique sequence?
example, b1=1 b2=2 b3=3 b4=4.
next sequence would then be b1=1 b2=2 b3=4 b4=3
Basically im looking for a loop that will try find every singe unique key sequence??

I guess you're referring to the VBA code above.
It's unclear to me just what you now want.
Would a fuller explanation and/or a somewhat expanded numerical example be possible?
e.g. do you want to fill B1:B26, check for uniques, fill B1:B26 again and check for uniques again, say 10 times and then give a list like
Run 1 Unique
Run 2 Not unique
etc.

all in the same session?
I'd need to know more about your specific data setup, and the sort of output you want.

 

[Hammerjoe]

I was wondering if there would be a an easy way to create a counter without using 26 for/next loops?

for a=1 to 26
for b=1 to 26
...
for z=1 to 26
call magic_code
next

Is there an easier way to replace all these for/next?

 

[kalak]

I was wondering if there would be a an easy way to create a counter without using 26 for/next loops?

for a=1 to 26
for b=1 to 26
...
for z=1 to 26
call magic_code
next

Is there an easier way to replace all these for/next?

It seems you're asking about nested loops, and to see if 26 loops within loops can be cut down to (say) 2.
I think that VBA doesn't make it easy (if at all possible) to do this, and that it's better to either use a workaround (i.e. handle the problem somewhat differently) or just face up to the 26 loops.
Perhaps if you'd be a bit more explicit about the specific problem that you want to use 26 nested loops to resolve ...
I've managed to abbreviate the multiple nested loop sort of thing in a couple of special cases, but I've never cracked it in general, nor have I seen anyone else do it.

 

[Hammerjoe]

I am just trying to find a simple code to replace 26 for/next loops.
I came up with something like this:

Sub solve()

a = 1
numberloops = 26
Call fornext(a, numberloops)
If a > numberloops Then a = numberloops
Do Until a = 0

Call fornext(a, numberloops)
If a > numberloops Then a = numberloops

Loop

End Sub

Function fornext(a, nloops)

Do While a <= nloops
DoEvents
num = ThisWorkbook.Sheets("sheet1").Cells(a, 2)
If num < nloops Then
ThisWorkbook.Sheets("sheet1").Cells(a, 2) = ThisWorkbook.Sheets("sheet1").Cells(a, 2) + 1
Else
ThisWorkbook.Sheets("sheet1").Cells(a, 2) = 0
a = a - 2
End If
a = a + 1
Loop


Its not pretty or especially efficient but it works.
Im wondering how else it could be done?