Unique Random numbers and Excluding old

[hkaldis]

Hi guys,

sorry if this is already answered somewhere i search alot and no luck finding it...

I have the following issue.


Lets assume i have a list in Column A (E.g. Names) and i would like to pick 3 members (never the same) of that list in the following Cells: B2 - C2- D2


Then in the bellow row (cells: B3 - C3 - D3) i would like again to pick 3 unique members of list with names but EXCLUDING the values that i have now in B2 - C2 - D2


Any suggestions?

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

You could add another column with random numbers in it e.g. =RAND().

Sort the data by the random number column.
Select the first three names for your first row and the next three names for the second row.

For a new trial just re-sort the data.

Excel 2013
	A	B	C	D	E	F
1	Name	n		Name 1	Name 2	Name 3
2	X8	0.52974		X8	X1	X7
3	X1	0.570586		X4	X17	X3
4	X7	0.012688
5	X4	0.505866
6	X17	0.583634
7	X3	0.251436
8	X2	0.66405
9	X5	0.417563
10	X14	0.901413
11	X13	0.803334
12	X11	0.539853
13	X18	0.155227
14	X16	0.932091
15	X15	0.905596
16	X10	0.958468
17	X9	0.076347
18	X19	0.469093
19	X6	0.877983
20	X12	0.716432
Sheet1

Cell Formulas
Range	Formula
D2	=A2
D3	=A5
E2	=A3
E3	=A6
F2	=A4
F3	=A7
B2	=RAND()
B3	=RAND()
B4	=RAND()
B5	=RAND()
B6	=RAND()
B7	=RAND()
B8	=RAND()
B9	=RAND()
B10	=RAND()
B11	=RAND()
B12	=RAND()
B13	=RAND()
B14	=RAND()
B15	=RAND()
B16	=RAND()
B17	=RAND()
B18	=RAND()
B19	=RAND()
B20	=RAND()

 

[hkaldis]

Thanks RickXL but my solution should not include sorting. So what i thought is to use the array trick with

=LARGE(ROW($1:$10)*NOT(COUNTIF($D$2:D2, ROW($1:$10))), RANDBETWEEN(1, 11-COLUMN(A1)))

For columns and the equivalent for Rows. My problem is that i am able to create the unique random number in the list and in D E F columns afterwords (so to bring with vlookup the name) but i cannot then exclude the already used...

Hi and welcome to the MrExcel Message Board.

You could add another column with random numbers in it e.g. =RAND().

Sort the data by the random number column.
Select the first three names for your first row and the next three names for the second row.

For a new trial just re-sort the data.

Excel 2013

	A	B	C	D	E	F
1	Name	n		Name 1	Name 2	Name 3
2	X8	0.52974		X8	X1	X7
3	X1	0.570586		X4	X17	X3
4	X7	0.012688
5	X4	0.505866
6	X17	0.583634
7	X3	0.251436
8	X2	0.66405
9	X5	0.417563
10	X14	0.901413
11	X13	0.803334
12	X11	0.539853
13	X18	0.155227
14	X16	0.932091
15	X15	0.905596
16	X10	0.958468
17	X9	0.076347
18	X19	0.469093
19	X6	0.877983
20	X12	0.716432

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =A2 E2 =A3 F2 =A4 D3 =A5 E3 =A6 F3 =A7 B2 =RAND() B3 =RAND() B4 =RAND() B5 =RAND() B6 =RAND() B7 =RAND() B8 =RAND() B9 =RAND() B10 =RAND() B11 =RAND() B12 =RAND() B13 =RAND() B14 =RAND() B15 =RAND() B16 =RAND() B17 =RAND() B18 =RAND() B19 =RAND() B20 =RAND() <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[XOR LX]

Hi.

Perhaps this will be of use to you:

Grid of Random Integers « EXCELXOR

Regards

 

[hkaldis]

Just got a headache by reading that Grid of Random Integers but it surely works. Thanks XOR LX!

Why just why they don't put another option at random formula RANDBETWEEN(MIN,MAX,EXCLUDING) it would be so much helpful instead of trying to understand the arrays...

Hi.

Perhaps this will be of use to you:

Grid of Random Integers « EXCELXOR

Regards

 

[XOR LX]

Just got a headache by reading that Grid of Random Integers but it surely works. Thanks XOR LX!

Ha! Are my explanations that bad?

Why just why they don't put another option at random formula RANDBETWEEN(MIN,MAX,EXCLUDING) it would be so much helpful instead of trying to understand the arrays...

Yes, perhaps you're right. Complementary operations are not something which Excel does well.

Only the other week someone was looking for a solution to the problem of excluding certain ranges from a larger, union of ranges, i.e. given the range e.g. A1:A10, C1:C20, E1:E30, remove just the cell C13.

Of course, the only way to do this (I think) is to redefine it as A1:A10, C1:C12, C14:C20, E1:E30. But if you've got a lot of single cells that you want excluding (which was the case for the OP), then such a process can be extremely time-consuming and hardly ideal.

Regards

 

[hkaldis]

Both your solution and explanation are awesome!

Thanks it a life saver...

out of curiosity, i tried to do the following

In C4 i created the following array:

=LARGE(ROW(INDIRECT($J$4&":"&$K$4))*NOT(COUNTIF($B4:B4, ROW(INDIRECT($J$4&":"&$K$4)))), RANDBETWEEN($J$4, $K$4-COLUMN(A1)+1))

Where in J4 is the minimum and K4 the maximum of randbetween.

This allows me to have unique numbers in columns and works fine. Now if i modify the *NOT part of the formula to:
*NOT(COUNTIF($B4:B4,ROW(INDIRECT($J$4&":"&$K$4)*NOT(COUNTIF($C3:$H3),ROW(INDIRECT($J$4&":"&$K$4)

i think it would work as i am excluding all values. My problem is that when i try it i get a too many arguments warning in excel

What you think?

I have it in an excel here:

https://dl.dropboxusercontent.com/u/26018924/unique random.xlsx

Ha! Are my explanations that bad?



Yes, perhaps you're right. Complementary operations are not something which Excel does well.

Only the other week someone was looking for a solution to the problem of excluding certain ranges from a larger, union of ranges, i.e. given the range e.g. A1:A10, C1:C20, E1:E30, remove just the cell C13.

Of course, the only way to do this (I think) is to redefine it as A1:A10, C1:C12, C14:C20, E1:E30. But if you've got a lot of single cells that you want excluding (which was the case for the OP), then such a process can be extremely time-consuming and hardly ideal.

Regards

 

[XOR LX]

You're effectively attempting to achieve what I did, though using a couple of COUNTIF statements in place of my use of FREQUENCY.

I think you've got a bit confused with your syntax, though, as well as the relative/absolute referencing for your ranges. What's more, if you try to avoid CSE by taking products of arrays like that, you'll find that Boolean FALSE returns end up as numerical zeroes, and so will potentially be returned.

Your formula in C4 would be:

=LARGE(ROW(INDIRECT($J$4&":"&$K$4))*NOT(COUNTIF($B4:B4,ROW(INDIRECT($J$4&":"&$K$4))))*NOT(COUNTIF($C3:$H$3,ROW(INDIRECT($J$4&":"&$K$4)))),RANDBETWEEN($J$4, $K$4-COLUMN(A1)+1))

though, as I said, you will need to convert this to a CSE set-up with some IF statements to be sure of correct returns; otherwise you will most likely find yourself with zeroes in many places.

I leave that conversion to you, if interested.

Regards

 

[RickXL]

Hi,

You could try a macro solution.

This one works just like my original idea except that instead of the list of random numbers being on the worksheet it is in an array or, more accurately, a sorted list. This list contains the random numbers and the occupied row numbers. That is, the one with names in. The list automatically sorts itself into random number order. This means that the first six row numbers can be used to locate the names in a random, non-repeating, sequence.

A couple of For Next loops take the first six names and write them to the worksheet.

The worksheet name can be changed by overtyping it.
Extra names in the list will automatically be included.
More output rows and columns can be added by changing the numbers on the final pair of For Next loops.

Sub Random_N()

    Dim N As Long, i As Long, iRow As Long, iCol As Long
    Dim mySL As Object
    Dim ws1 As Worksheet
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set mySL = CreateObject("System.Collections.SortedList")
    
    With ws1
        ' See how many names there are in column A and set the list size
        N = .Cells(.Rows.Count, "A").End(xlUp).Row
        mySL.Capacity = N - 1
        
        ' Fill array with random numbers and row numbers
        For i = 2 To N
            mySL.Item(Rnd) = i
        Next
        
        ' Write out the random names
        i = 0
        For iRow = 2 To 3       ' Row numbers for output
            For iCol = 2 To 4   ' Column Numbers for output
                .Cells(iRow, iCol).Value = .Cells(mySL.GetByIndex(i), 1).Value
                i = i + 1
            Next
        Next
        
    End With

End Sub

 

[hkaldis]

Thanks Rick this is also very helpful

I ll try both as in large tables i think the array will be slow

Hi,

You could try a macro solution.

This one works just like my original idea except that instead of the list of random numbers being on the worksheet it is in an array or, more accurately, a sorted list. This list contains the random numbers and the occupied row numbers. That is, the one with names in. The list automatically sorts itself into random number order. This means that the first six row numbers can be used to locate the names in a random, non-repeating, sequence.

A couple of For Next loops take the first six names and write them to the worksheet.

The worksheet name can be changed by overtyping it.
Extra names in the list will automatically be included.
More output rows and columns can be added by changing the numbers on the final pair of For Next loops.

Sub Random_N()

    Dim N As Long, i As Long, iRow As Long, iCol As Long
    Dim mySL As Object
    Dim ws1 As Worksheet
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set mySL = CreateObject("System.Collections.SortedList")
    
    With ws1
        ' See how many names there are in column A and set the list size
        N = .Cells(.Rows.Count, "A").End(xlUp).Row
        mySL.Capacity = N - 1
        
        ' Fill array with random numbers and row numbers
        For i = 2 To N
            mySL.Item(Rnd) = i
        Next
        
        ' Write out the random names
        i = 0
        For iRow = 2 To 3       ' Row numbers for output
            For iCol = 2 To 4   ' Column Numbers for output
                .Cells(iRow, iCol).Value = .Cells(mySL.GetByIndex(i), 1).Value
                i = i + 1
            Next
        Next
        
    End With

End Sub