VBA code - identify a list to use in a survey

[Azza87]

Hello,

I am trying to write a VBA macro/code that allows me to generate a list of 100 companies to use for a survey based on a certain parameter "Size_Category". There are 3 categories and each of the categories have an identified a sample size required to be included in the final survey population of 20 companies.

Because I want to ensure that the survey population that is selected is not objective, I added a random number. I thought of recording a macro using the methodology that I have defined below:

1) Generate random number for the company list.
2) Filter list based on Size_Category
3) Sort list based on Random #
4) Copy number of companies based on the population number required
5) Repeat steps 2 to 4 for the other size_categories

However, I fear that by resorting to this methodology I might be a bit restricted, and the macro will be a little bit clunky and confusing to edit.

Any ideas on how I can setup a vba code to facilitate this process? I attached a file with a sample list to put things into context.

Many thanks!

Azza

 

[vletm]

Azza87
... or something like this - press [ Do It ]

 

[p45cal]

In the attached there's a pivot table at cell N11.
Refreshing it returns a new random selection.
You can use the dropdown in cell P11 to choose the company size.

You can choose the sample size by using the dropdown in N11:



then choose your sample size:



Then you can copy paste-Special, Values to somewhere else.

Otherwise, there's a button which does this at cell G11. Repeated clicks of the button provide a new table.

It uses the values in cells J2 to J4 for the sample sizes, and uses cells I2 to I4 for the size categories.
Note that in column C, instances of small all had a trailing space after them; I removed those trailing spaces. Just note that values in column I must match exactly the size category text in column C.