Solved random sampling excel vba

Mstest / Awrdacpi
February 10, 2015 at 18:21:21
Specs: Windows XP, 2 GHz / 958 MB
Hi,

I actually have a data where there is 2 classification - Approve and Missing
I need to have a random sampling 50 for each classification but these classification are accompanied by a company in which I need to proportionately distribute the samples to all available company.

Like for example for the Missing classification
I have 18 unique companies in it so guaranteed I will have 18 missing classification but the remaining 32 missing classification needs to be randomly selected.

I do not know how to do it. please help.

Thank you


See More: random sampling excel vba

Report •


#1
February 11, 2015 at 11:27:34
It can be done without a macro by using a series of formulas and some Helper Columns. Helper Columns are where you place formulas to produce intermediate results which can then be used for the final calculation.

In this example, I am going to shorten your list to 10 total entries with a requirement of 6 random samples. 3 of the entries will have "companies" associated with them, so I will need to find 3 random values from the remaining 7. (I shortened the list simply to make it easier to post.)

It would probably help if you replicated this sample to get a feeling for how the process works and then modify it to fit your exact needs. The Helper Columns (C & D) can be placed anywhere you want or simply hidden. Just make sure that you refer to the correct columns, wherever you choose to put them. I put them in the order that the process runs so that it is a little easier to understand.

I am going to start with this:

         A         B           C       D       E        F     
1    Missing    Company     Random    Rank   Result    Qty
2        AA       11                                    6
3        BB
4        CC
5        DD       22
6        EE
7        FF       33
8        GG
9        HH            
10       II
11       JJ

In F2, I entered 6, which is the total number of samples I need, including both the ones that have companies assigned and the ones that don't. In other words, that is your "50".

In C2 I entered the following formula and dragged it down to C11. This will place a random number next to each entry that does not have a company associated with it.

=IF(B2="",RAND(),"")

In D2 I entered the following formula and dragged it down to D11. This will assign a unique number to each of the random values in Column C:

=IF(C2="","",RANK(C2,$C$2:$C$11))

In E2, I entered the following formula and dragged it down to E11. This formula will refer to the value in F2 (e.g. 6) and calculate how many random selections to make. It will then use that number and return values from Column A that have a "ranking" that is less than that value, as well as the values from Column A that have a company associated with them.

=IF(D2<>"",IF(D2<$F$2+1-COUNTA($B$2:$B$11),A2,""),A2)

Since the RAND function values will change every time the sheet calculates, a different set of ramdom values from Column A will be chosen along with the values that have a company assigned.

Here is the output from one such sampling. You will note that the 3 random values returned in Column E all have a "ranking" of 3 or below, which is the number of random samples required (Qty 6 minus 3 with associated companies).

         A         B           C       D       E        F     
1    Missing    Company     Random    Rank   Result    Qty
2        AA       11                           AA       6
3        BB                  .903      2       BB
4        CC                  .876      3       CC
5        DD       22                           DD
6        EE                  .728
7        FF       33                           FF
8        GG                  .086      7
9        HH                  .981      1       HH           
10       II                  .191      6
11       JJ                  .071      4

Let me know how this works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 16, 2015 at 21:05:24
Hi DerbyDad03,

Thank you for this. I find that there are some lapses on this.

It is working but it doesn't get all the company that I have.

Here is my sample data

Company    	         Classification
220	                    Approval
220	                    Missing
220	                    Missing
220	                    Approval
220	                    Approval
220	                    Missing
220	                    Missing
220	                    Approval
220	                    Approval
485	                    Approval
494	                    Approval
662	                    Missing
662	                    Missing
662	                    Missing
662	                    Approval
748	                    Missing
796	                    Approval
796	                    Approval
6148	                    Missing
6148	                    Missing
8200	                    Approval
8200	                    Approval
8200	                    Approval
8200	                    Missing
8200	                    Missing
8200	                    Missing
8202	                    Approval
8202	                    Approval
8202	                    Missing
8202	                    Approval
8203	                    Missing
8203	                    Missing
8203	                    Missing
ZPH1	                    Approval


When I use the forumla, yes it is working but wasn't able to get all the company present in the data. In this example I have 8 unique company for Approval and 7 unique company for Missing.

I also have 17 total approvals and also 17 missing. Let's say I need to get 10 random sample for each classification but should be proportionate to company. If I have 8 company in approval I need to get all of these 8 and another 2 to have a total of 10. Same goes to Missing.

Hope I explain it correctly.
Thanks

message edited by shieldbreakers


Report •

#3
February 17, 2015 at 04:19:51
✔ Best Answer
There was nothing in your original post that indicated multiple entries for the same companies, so my formula method does not account for that. I took the use of the word "unique" to mean one entry for each company.

Is is possible to use a filtered list so that there is only a single instance for each company? The Advanced Filter feature can create that list for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 27, 2015 at 03:10:08
Thank you DerbyDad03..

I just used the Rank formula.


Thanks a lot for your help.


Report •

Ask Question