Generate a random set of numbers without duplicates

[jeffreybrown]

I have a dis-contiguous set of cells such as B4:E5, B8:E13, B17:B18, and B27:E27. 44 cells in total.

What I'm trying to do is generate a name from a list in column G. There are 52 items in the list, but I only need to randomly generate 44 unique names.

I've messed around with Rand() and MRAND() from the MoreFunc, but can't seem to find the right solution.

Any thoughts?

[ben_hensel]

Hmm... how about assigning each cell an indexing number from 1-44, then giving each of the 52 items a number with RAND(), then assigning them arbitrarily based on the ranking of the random numbers for each item? No error handling for duplicates, just assume that RAND producing the exact same number twice is sufficiently unlikely.

[Pete_UK]

Hi Jeff,

you could put this in H3:

=RAND()

and this in I3:

=RANK(H3,$H$3:$H$54)

then copy down to row 54. Then you could just use an INDEX/MATCH in your group of cells to get the 1st, 2nd, 3rd etc. in sequence up to 44.

Hope this helps.

Pete

[AlKey]

Hi Jeff,

Try this array formula

Please Login or Register  to view this content.

[jeffreybrown]

Thanks Ben, but this may not work as I would have to redesign many parts of my sheet.

Thanks Pete. This is what I started with, but hoping I could find a solution to do the RAND() part directly in the cell. From the MoreFunc, MRAND()+1 works will, but it doesn't continue with non-contiguous cells. If I would, I can easily use, {=INDEX($G$3:$G$54,MRAND()+1)}, but can't get it to work with my configuration.

In the end I might have to use the Rand() method.

[Pete_UK]

Well, here's the workbook. I used this in B4:

=INDEX($G:$G,MATCH(4*COUNTA(B$3:B3)+COLUMNS($B:B),$I:$I,0))

and copied across into C4:E4, then copied those 4 cells into the cells below with borders.

Hope this helps.

Pete

[jeffreybrown]

Hi AlKey, this seems to be generating duplicates, but Pete's non array formula just might work out fine and is not creating duplicates.

I'll now implement this into my sheet and see how it goes.

I do the scheduling for Sunday worship church duties and just trying to figure out a way to randomly fit names and then tailor for overlap from there.

When I say overlap, it's because there are other duties which run off of a different rotating schedule and just playing around with the best fit versus having to manual fill out the sheet.

Thanks everyone...

[Phuocam]

Try this in B4:

=INDEX($G:$G,SMALL(IF(COUNTIF($A4:A4,$G$3:$G$54)+COUNTIF($B$3:$E3,$G$3:$G$54)=0,ROW($G$3:$G$54)),
RANDBETWEEN(1,52-COUNTA($A4:A4,$B$3:$E3))))