Splitting data into different but equal random samples

Question

I have 3 data sets (eg Scores from three different Universities) all mixed up in a single excel sheet and with different number of scores. Eg Oxford (AT1) sample with 4000 scores, Cambridge (AT2) with 3200 scores, and Portsmouth (AT3) with 1800 scores and I want to trim them so that the sample sizes match the university with the lowest number of scores (in this case 1800), what would the macros look like? Please note the spreadsheet has 2 columns i.e. University and Score. Please see picture below

Maskell_Rascal · Accepted Answer

Hey @Blessing

Unfortunately it is not random, but in order of the rows of the data. You can get a randomized sampling, but you'd need to spit your data by University to do it. The Random % tool lets you set the number of random records you want by percentage or fixed number, so this would be your best option.

I've attached a sample workflow for you to try out.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Maskell_Rascal · Accepted Answer

@Blessing One other option I just thought of would be to use a formula tool to assign a random value, sort by University and random value, and then use the Sample tool to select your desired number of rows for each group.

I've attached that example for you as well.

Again, let me know if this solves your issue or not.

Thanks!

Phil