Non Repeating Output List from Excel Equal to Input list

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

dlkorinek,

[noparse]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.[/noparse]

HTH,
Maud

40091-Index1

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 for the random number.

zeddy

Reply | Quote