Sampling - Please check

[SG]

Hi Everyone,

I need your help in choosing the samples for quality check. The only idea in my mind is the Random function but this wouldn’t suffice as there are 2-3 conditions on basis of which sampling should be done.

It should be done for 4 calls per month,per person & per type (column A for type).
Different Duration slabs like out of 4 calls for each type,

One call with Duration of 1 to 10 minutes
One call with Duration of 10 to 20 minutes
One call with Duration of 20 to 30 minutes
One call with Duration of 30 to 40 minutes

In case, any slot not available, it should skip that slot & choose the call from next slab.
I really know it’s very tricky but need your help. PFA data for your reference.

Thanks in advance

 

[NARAYANK991]

Hi ,

The data available in the file is inadequate to get 4 sample calls per person per type. Upload a file which contains enough data.

The randomness of selecting the samples is inherent in the data ; nothing much needs to be done.

If the data is sorted in the following order :

Type
Person
Duration

it will be easy to create a field which will have Slot1 , Slot2 , Slot3 or Slot4 based on the call duration , and prefix it by the type and person so that selecting the first 4 samples from each combination of type , person and slot will give you the result.

Narayan

 

[SG]

Hi Narayan,
Please find updated sheet for your reference. I have added the data. However, if for any person, calls can't sum up to 4, then it can select the sample less than 4 calls per month. Please help me with the maximum automation on this.

 

[NARAYANK991]

Hi ,

I am still not clear on what automation you are looking for. The data is inadequate to do any sampling.

I have introduced 2 columns , one of which inserts the slot number based on the duration , and the last column contains either Hide or is blank. The text Hide will be inserted using the formula , if the count of entries with the same type , person and slot is more than 4.

With this set up , all that is to be done is to filter the last column for blanks i.e. all those rows which have the text Hide in this column will be hidden , and what is left will need to be used as samples.

Narayan

 

[SG]

Thanks Narayan. I'll get back to you in case of any query.

 

[SG]

Hi Narayan,

I have to display the samples in other sheet not in the raw data. Can you please modify the sheet as I have to select randomly 4 samples per person,per month with duration as per the slabs.
The formula given by you selected the samples only on basis of duration. Can you please check this.

 

[NARAYANK991]

Hi ,

The formula in column F ( labelled Slots ) is putting in the Slot number depending on the duration of the call.

However , the formula in column G ( labelled Status ) is putting in the text Hide based on the type , name and slot being the same.

This will work only if the data itself is sorted in the following order :

Type
Name
Duration

You can see that there are 11 entries against Rohit , but they have been separated into 2 groups , one of 8 entries against the basic type , and the remaining 3 entries against the feature type.

Within the 8 entries of the basic type , they are sorted based on the Duration , so that all entries in Slot1 are together , all entries in Slot2 are together and so on.

Thus , the Hide text will appear only if there are more than 4 entries with the same type , name and slot. This is the reason I mentioned that your data is not sufficient. If you have thousands of entries , you will see more than one row having Hide in the Status column.

Transferring the entries from one sheet to another is just a matter of doing the following :

1. Click on the AutoFilter arrow and select (Blanks) , so that only those rows which do not have Hide in the Status field are selected.

2. Select the entire range of data from A1 through till the last cell in column G ; you can do this by selecting the entire first row of data ( A1:G1 ) , and then pressing the keys END DOWN.

Copy this selection and paste it in a new sheet.

Narayan