Random Sampling Based on Weighted Criteria

[bObaZ]

Hello,

I'm trying to find a formula (Or VBA, but i have close to zero experience on that) that retrieves me a series of random samples based on a weighted criteria.

Let's say i have the following Table

Jon	1
Mike	2
Jon	3
Jon	4
Jon	5
Mike	6
Mike	7
Zero	8
Zero	9
Jon	10

<tbody>
</tbody>

So i have 5 ocurrence of Jon, 3 for Mike and 2 for Zero. This is my Universe.

I want to get a result which is a list of random selected Samples of 3 Jons, 2 Mikes and 1 Zero selected Randomly.

this is just an Example My list has 6k entries and i need a sample of 200 with different weights according to the entries.

Is this possible?

 

[bObaZ]

Normally what I do is Creating and auxiliary Column with the =RAND() function. Then I proceed do Filter by the Entry name i want and Sort and take the top n rows according to the Sample that i need for that Entry.

The problem is that normally i have around 36 unique entries all with different sample sizes, and it takes me around 1 hour to do all the sorting and filtering.

Is there anyway you can think of mechanizing this?

 

[shg]

	A​	B​	C​	D​
1​	Name​	Num​
2​	Jon​	3​
3​	Mike​	2​
4​	Zero​	1​
5​
6​	Name​	Pick​
7​	Jon	​		B7: =IF(RAND() < (VLOOKUP(A7, $A$2:$B$4, 2, FALSE) - COUNTIFS(A$6:A6, A7, B$6:B6, "x")) / COUNTIF(A7:A$16, A7), "x", "")
8​	Mike	x​
9​	Jon	x​
10​	Jon	​
11​	Jon	x​
12​	Mike	x​
13​	Mike	​
14​	Zero	x​
15​	Zero	​
16​	Jon	x​

 

[bObaZ]

Hello shg and thank you for your help.

Unfortunatelly it doesn't work. The original table (A2:B4) must be sorted?

 

[shg]

It does work, and the table need not be sorted.

​	Name​	Num​	Check​
	Dana	3​	OK​	D2: =IF(COUNTIFS($B$9:$B$58, B2, $C$9:$C$58, "x") = C2, "OK", "oops")
	Barb	2​	OK​
	Eric	4​	OK​
	Cain	4​	OK​
	Alan	5​	OK​
Case​	Name​	Audit​
1001​	Eric	​		C9: =IF(RAND() < (VLOOKUP(B9, $B$2:$C$6, 2, FALSE) - COUNTIFS(B$8:B8, B9, C$8:C8, "x")) / COUNTIF(B9:B$58, B9), "x", "")
1002​	Eric	​
1003​	Eric	x​
1004​	Dana	x​
1005​	Alan	x​
1006​	Dana	x​
1007​	Alan	x​
1008​	Eric	​
1009​	Eric	​
1010​	Eric	x​
1011​	Alan	x​
1012​	Alan	​
1013​	Barb	​
1014​	Alan	​
1015​	Cain	​
1016​	Eric	​
1017​	Dana	x​
1018​	Cain	x​
1019​	Cain	​
1020​	Eric	​
1021​	Cain	x​
1022​	Eric	x​
1023​	Barb	​
1024​	Barb	​
1025​	Barb	x​
1026​	Barb	x​
1027​	Barb	​
1028​	Barb	​
1029​	Dana	​
1030​	Alan	​
1031​	Alan	​
1032​	Eric	​
1033​	Cain	x​
1034​	Eric	​
1035​	Barb	​
1036​	Eric	​
1037​	Alan	​
1038​	Alan	x​
1039​	Cain	x​
1040​	Barb	​
1041​	Alan	​
1042​	Barb	​
1043​	Eric	​
1044​	Eric	​
1045​	Alan	​
1046​	Eric	x​
1047​	Barb	​
1048​	Barb	​
1049​	Alan	x​
1050​	Alan	​

 

[bObaZ]

Hello shg,

Thank you so much for your help. It's working now. It wasn't working because I had a larger universe with Entries that were not on the initial table. I removed all the "trash" entries and it's now Working!

You are a life saver!

 

[shg]

You're welcome.