Thread: Non Repeating Output List from Excel Equal to Input list

1. Non Repeating Output List from Excel Equal to Input list

I have this list in a column... 20 different values... I want to generate a random output list from the input list and not repeat. I have used =INDEX(\$D\$1:\$D\$20,RANDBETWEEN(1,20)) in each of the 20 cells but I get repeaters. The values in Col D are all alpha numeric. Is there something else I need to add to make sure that none of the outputs repeat? I am attaching a file in case you need to see. Look for RandQR_B.xlsx Thanx so much for your assistance.

DennisK

2. This is a 2-column process in Excel.
Create a column with the numbers from 1 to 20.
Create another column next to it with =rand() filled down all 20 rows.
Then, sort the column with the random numbers but include the first column in the sort.
Then use that rearrangement of the first column for your selection of your data in D.

I'm sure someone will send you a UDF that will bypass this issue.

3. The Following User Says Thank You to kweaver For This Useful Post:

Maudibe (2015-04-04)

4. dlkorinek,

Consider this approach:
1. Column D1:D20 has your values
2. Cell H1 has the formula =COUNTIF(G\$1:G1,G1)>1 copied down to H20. This column will yield True for repeat or False for unique for the adjacent cell in column G which is populated with numbers 1-20 by running the code.
3. The code places a random number from 1-20 in each cell in column G until its adjacent cell turns False (validating it is unique)
4. Cell E1 has the formula =INDEX(\$D\$1:\$D\$20,G1,1) copied down to E20 which looks at the unique numbers in Col G and indexes the values in col D.

HTH,
Maud

Index1.png

Place in a standard module:
Code:
```Public Sub Unique()
Dim cell As Range, rng As Range
Set rng = Range("D1:D20")
For Each cell In rng
cell.offset(0, 3) = WorksheetFunction.RandBetween(1, 20)
Do While cell.offset(0, 4) = True
cell.offset(0, 3) = WorksheetFunction.RandBetween(1, 20)
Loop
Next cell
End Sub```
Columns G and H can be hidden for cosmetic reasons or placed on a different page (the code will need some readjustment). Also, the functions of both columns G and H could have been incorporated into the code but it would have been much more complex and difficult to follow.

5. Thanks so much for your input... was hoping to avoid vba as the person I am doing this for wants it as simple as possible but will take a look at it.

6. Once you have it set up, it is seamless and works well for repetitive sampling. However, if this is a one time process, KW's approach might be the best for you but as he states, you must copy and paste special the rand values or they will recalculate with the next sheet calculation.

7. Hi

The attached file uses 1 line of code.
Just switch to [Sheet2] and then back to [Sheet1] to see the new random list in column [E].

The code on [Sheet2] is:
Code:
```Private Sub Worksheet_Activate()
[a1].Sort key1:=[b1]
End Sub```
This method uses kweavers suggestion in post#, with a hidden column [B] for the random number.

zeddy

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•