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

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 JJIn 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 4Let me know how this works for you.

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

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

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.

Thank you DerbyDad03.. I just used the Rank formula.

Thanks a lot for your help.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History