# VBA Verify that array has unique values

#### Hammerjoe

##### Board Regular
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?

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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..
Code:
``=IF(SUMPRODUCT((COUNTIF(B1:B26,B1:B26)))=26,"Unique","Not Unique")``

If your values in the array are all positive integers (1 to 26) you can try this bit of VBA
Code:
``````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``````

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??

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.

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?

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.

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?

Replies
1
Views
152
Replies
2
Views
203
Replies
1
Views
131
Replies
1
Views
235
Replies
1
Views
446

1,196,358
Messages
6,014,795
Members
441,847
Latest member
hw407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back