# Thread: Help Required : Random Sampling of Data

1. Sorry - 200 was just a guess for howmany Facility1 lines there were

I made "High Values" a fourth option button since it's logic (as I understand) has nothing to do with the Equal or Proportional random sampling

Capture.JPG

Capture2.JPG

2. Thanks a lot Paul for your time and extended help!
I agree picking high values doesn't match with the word random in the page heading itself "Stratified Random Sampling".
So, I will try to understand your codes and try to make a fifth option for high values based on the proportionate random size. This infract will be duplicating the options, that's the reason I though to have checkbox option.
I think, instead of adding another option, I can keep the original 3 options and call the subs according to the checkbox checked or not.
Next step, I'm also thinking to pick samples only for the strata values selected from the list box.

3. In post #36 ...

I wanted the sample size to be considered like how it was working previously.

And if the check box is checked, it shall pick in the order of highest to lowest values instead of random pick.

If the checkbox is unchecked, then random pick, the way it was working previously
I think that is what I have in there now.

It picks the same number (a user input = 5) of highest values of another column (= SubAmt) for each Strata (=Facility Name)

I think it's cleaner and easier to maintain using different Option Buttons for each choice, instead of trying to mix multiple options

Are you thinking that the number selected ( = 5 above) from each Strata (=Facility Name) will vary proportionally?

You'd have to use a percentage figure I think

Example:

Strata = Facility Name
Other = SubAmt

Percentage input = 1%

Facility 1 has 10,000 records so select 100 highest SubAmt
Facility 2 has 1,000 records so select 10 highest SubAmt
Facility 3 has 5,000 records so select 50 highest SubAmt
Facility 4 has 20,000 records so select 200 highest SubAmt

4. Thanks Paul!
I will try it out

5. Hi Paul,
Hope all well at your end
Basis of understanding some part of your codes, I could do the coding for proportionate samples from a high value filed. I have also made some cosmetic changes in the selection criteria and related codes. However, there is no change in the main codes.

I'm back again as I have a problem here with the sample file attached. In case if the number of strata is more, the tool is getting stuck as I understand it loops from the first row till last row every time for each strata. Is there any way around to resolve this issue. For example, VENDOR_NAME in the attached file.

I had slowness in viewing the strata values (View Statistics) because of the same looping which was later resolved by replacing for looping with countifs.
Could you please look in to it whenever you have time

6. 1 skipped the ProgressBar updating and used Application.StatusBar for speed

Try picking VENDOR Equal and Proportional and see if it's faster

7. Thanks a lot Paul!
Both equal and proportionate are working faster for the random pick method.
I will try the high value pick option by using your above method (storing the entire data range in an array)

8. One doubt
How can I exclude if there is a heading for the data range without converting to a Table?

Attachment 28953
For example in the above case, the first row should be 3 not 2. And it has to be dynamic, there may or mayn't be a heading.
Is it possible to consider the first row which has the last used column, something like that?

9. You could probably make an assumption that if the first row is all Strings and some cell in the second row is NOT a String, then there are headers

Or

Use the Custom Sort approach and get input from the user

Capture.JPG

10. Thanks Paul!
I will check out that option

11. Hi Paul,

I was testing this tool in different work books and I would request your help in the following 2 areas-

(1) How can I sort the strata list based on the count in descending order and show in the list box
`UF_StrataList.lbSummary.List = aryList`
(2) How can I pick samples only for the selected strata item from the list box. In the attached example, if I select the column "DenialCode", 90% of the line items are "blank". Hence, I want to skip the strata "blank" and pick the samples only from few selected strata items.
If there is a selection, pick samples only from the selected strata or else pick from all