Good Morning.
I have a sheet with three columns (well more but only three are relevant)
Col A is Task
Col B is Staff ID
Col C is competency level (1-3)
Depending on the task picked the result can be from 0 rows to 5000+
This data is extracted via macro from a larger sheet with multiple tasks and filters it down for QA purposes.
What I need to do is sample this sheet according to the following rules...
1) If only one instance of a task that instance must be selected
2) Sample should be split according to competency sample sizes. So for the levels below the following percentages should be sampled.
Level 1 = 2%
Level 2 = 3.5%
Level 3 = 5%
3) At least one instance per agent and an even distribution amongst agents on larger sample sizes also following rule 2.
I'm not even certain that this is possible in Excel, even someone saying 'it can be done' would be a start.
I do have another column that is a CONCANTENATE of the three Columns above if that's useful.
At the moment I can obviously create a random number for sampling purposes but have no idea where to start applying the rules and targeting it.
Any help greatly appreciated. I haven't published a data sample, but, can if required.
Regards, and, have a good weekend.
DaveA
I have a sheet with three columns (well more but only three are relevant)
Col A is Task
Col B is Staff ID
Col C is competency level (1-3)
Depending on the task picked the result can be from 0 rows to 5000+
This data is extracted via macro from a larger sheet with multiple tasks and filters it down for QA purposes.
What I need to do is sample this sheet according to the following rules...
1) If only one instance of a task that instance must be selected
2) Sample should be split according to competency sample sizes. So for the levels below the following percentages should be sampled.
Level 1 = 2%
Level 2 = 3.5%
Level 3 = 5%
3) At least one instance per agent and an even distribution amongst agents on larger sample sizes also following rule 2.
I'm not even certain that this is possible in Excel, even someone saying 'it can be done' would be a start.
I do have another column that is a CONCANTENATE of the three Columns above if that's useful.
At the moment I can obviously create a random number for sampling purposes but have no idea where to start applying the rules and targeting it.
Any help greatly appreciated. I haven't published a data sample, but, can if required.
Regards, and, have a good weekend.
DaveA
Last edited:
