I have a list of say 500 samples, which includes some duplicates.
I want to randomly assign all 500 samples into 20 groups (25 in each group). I can do this using rand() and a helper column.
However, I don't want any of the 20 groups to contain duplicates within them i.e. I want all 25 members of each group to be unique.
How can I do this please? Thanks
I have assumed that you are using Excel 2007 or later - Vista is an operating system version, but is not a version of Excel.
Let's say that your samples (with duplicates) are in column A, and your helper column of group assignment formulas is column D, and both start in row 2.
In E2, use the formula
=COUNTIFS(A:A,A2,D:D,D2)
and copy down to match.
Then in F2, use the formula
=MAX(E:E)
and press F9 until F2 is 1, which will indicate that each group has no repeated values. Depending on your number of repeats, it may take a few or many recalcs.
Once that value is 1, copy column D and paste special values so the values are frozen. You could also use a macro to do the same thing.
Last edited by Bernie Deitrick; 05-07-2015 at 10:25 AM.
Bernie Deitrick
Excel MVP 2000-2010
Thanks!
That does a great job at checking if the randomisation has produced the desired pattern - much appreciated.
Does anyone know a formula to ensure that duplicate samples are never put in the same group in the first place? It's basically, sampling without replacement, but also without duplicates.
The solution would depend on how you are doing your binning - but assuming that you are just ranking the random values, something like this that distributes the repeats evenly but randomly through the population may work in place of your simple RAND() usage:
=IF(COUNTIF($A$2:A2,A2)=1,RAND()*((COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2)+1)/COUNTIF(A:A,A2)-(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2))+(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2),VLOOKUP(A2,A:B,2,FALSE)*((COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2)+1)/COUNTIF(A:A,A2)-(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2))+(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2))
Give it a try with the previous checking formulas in place - if it works, no recalc will result in a MAX of more than 1.
Of course, if you don't mind other columns, then using this in C2:
=(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2))/COUNTIF(A:A,A2)
and this in D2:
=(COUNTIF(A:A,A2)-COUNTIF($A$2:A2,A2)+1)/COUNTIF(A:A,A2)
Allows that formula to become
=IF(COUNTIF($A$2:A2,A2)=1,RAND()*(D2-C2)+C2,VLOOKUP(A2,A:B,2,FALSE)*(D2-C2)+C2)
Last edited by Bernie Deitrick; 05-07-2015 at 01:09 PM.
Thanks, that's an interesting idea, and helps reduce the number of duplicates, if not eliminating them entirely.
My problem now is that my actual dataset is much larger - about 100000 values, and the calculations take forever.
I don't suppose you know a way to speed things up?!
Thanks again
That would - especially since you said 500 samples. You also never said how many duplicates you are actually talking about? 0.1% 1% 10% 50%?
It may be quicker to sort the samples into random order, and then use a macro to find the duplicates within blocks and switch them with one another. But how many duplicates you actually have and the block size would have an impact on its efficacy.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks