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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Jon[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Zero[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Zero[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Name​

[/td][td="bgcolor:#F3F3F3"]

Num​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

Jon​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

Mike​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

Zero​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#F3F3F3"]

Name​

[/td][td="bgcolor:#F3F3F3"]

Pick​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]Jon[/td][td="bgcolor:#CCFFFF"]

​

[/td][td][/td][td="bgcolor:#CCFFFF"]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", "")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Mike[/td][td="bgcolor:#CCFFFF"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Jon[/td][td="bgcolor:#CCFFFF"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Jon[/td][td="bgcolor:#CCFFFF"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]Jon[/td][td="bgcolor:#CCFFFF"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Mike[/td][td="bgcolor:#CCFFFF"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Mike[/td][td="bgcolor:#CCFFFF"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Zero[/td][td="bgcolor:#CCFFFF"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]Zero[/td][td="bgcolor:#CCFFFF"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]Jon[/td][td="bgcolor:#CCFFFF"]

x​

[/td][td][/td][td][/td][/tr]
[/table]

 

[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.

[Table="width:, class:grid"][tr][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Name​

[/td][td="bgcolor:#F3F3F3"]

Num​

[/td][td="bgcolor:#F3F3F3"]

Check​

[/td][td][/td][/tr]
[tr][td][/td][td]Dana[/td][td]

3​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td="bgcolor:#CCFFFF"]D2: =IF(COUNTIFS($B$9:$B$58, B2, $C$9:$C$58, "x") = C2, "OK", "oops")[/td][/tr]
[tr][td][/td][td]Barb[/td][td]

2​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td][/td][/tr]
[tr][td][/td][td]Eric[/td][td]

4​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td][/td][/tr]
[tr][td][/td][td]Cain[/td][td]

4​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td][/td][/tr]
[tr][td][/td][td]Alan[/td][td]

5​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td][/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#F3F3F3"]

Case​

[/td][td="bgcolor:#F3F3F3"]

Name​

[/td][td="bgcolor:#F3F3F3"]

Audit​

[/td][td][/td][td][/td][/tr]
[tr][td]

1001​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td="bgcolor:#CCFFCC"]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", "")[/td][/tr]
[tr][td]

1002​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1003​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1004​

[/td][td]Dana[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1005​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1006​

[/td][td]Dana[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1007​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1008​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1009​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1010​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1011​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1012​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1013​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1014​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1015​

[/td][td]Cain[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1016​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1017​

[/td][td]Dana[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1018​

[/td][td]Cain[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1019​

[/td][td]Cain[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1020​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1021​

[/td][td]Cain[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1022​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1023​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1024​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1025​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1026​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1027​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1028​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1029​

[/td][td]Dana[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1030​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1031​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1032​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1033​

[/td][td]Cain[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1034​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1035​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1036​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1037​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1038​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1039​

[/td][td]Cain[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1040​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1041​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1042​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1043​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1044​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1045​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1046​

[/td][td]Eric[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1047​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1048​

[/td][td]Barb[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

1049​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td]

1050​

[/td][td]Alan[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[/table]

 

[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.