Hi Paul, I really appreciate your help. Apologies for all the confusion.
I have added some more data in this file. I just need if i select Jan Month, so who ever agents has 4 calls with duration as per the slab must come in sample & if less than 4, still it would come with number of calls it has. It must cover all agent with number of calls they have in that month. Honestly speaking, You have really done the efforts in this file but my purpose is not getting solved neither i'm able to do it. :( If you can again relook this..please check otherwise..really thanks Regards SG On Friday, January 29, 2016 at 7:25:41 PM UTC+5:30, Paul Schreiner wrote: > > See below: > *Paul* > ----------------------------------------- > > > On Friday, January 29, 2016 3:19 AM, SG <sona...@gmail.com <javascript:>> > wrote: > > > > Hi Paul, I got the sheet. Thankyou so much for all your effort in this > sampling. I'm really thankful. > > Only one thing i observed is that, in raw data , there are 4 entries of > Rohit in Jan but in Random Sampling section, only two are coming as sample > for Rohit whereas it should be 4calls,per person, per month with available > slots as per the slab. PFA for you. > *I don't think that is a reasonable expectation.* > *In January, rohit may appear 4 times, but it only appears TWICE in the > 0-10 minute category, ONCE in the 10-20 minute and ONCE in the 20-30 > minute.* > > *In the sample data, there are (5) records in the 0-10 minute category for > January.* > *With only (4) people, one of the (5) records will not be selected.* > *When the data is sorted by the Rand" column, there is a "random" chance > that only one of the rohit records will be selected.* > > > Also, Rand function don't have any impact in selection of samples. Am i > right? > Please check if this can be solved as this is the condition for sampling. > *In order for the RAND() function to affect the sampling, you must sort > the data.* > *When you sort it by the Rand column, the "index" number gets recalculated > based on the "position".* > *so, each time you sort the data, you'll get a different set of selections > for the (4) people.* > > *Attached you will find a version of the file with a "sort" button.* > > > On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote: > > No Paul, I didn't get it. Can you please share it again. > > On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote: > > Yeesss.. and responded with an update. > Did you not receive it? > > *Paul* > ------------------------------ ----------- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John > Wesley*------------------------------ > ----------- > > > On Thursday, January 28, 2016 12:43 AM, SG <sona...@gmail.com> wrote: > > > > Paul, have you checked it? > > On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote: > > Thanks for your quick response Paul. The line ""In case, any slot not > available, it should pick so on" means that in case any slot is not > available, it should skip it & choose the call from next slab. Your trick > is helping me very much. One more modification i need is per person > sampling. That means, per person, 4 calls/month for all 4 slabs. > Please try one more time. > > On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner > wrote: > > Here's a possible solution. > Basically, you need to determine which "set" the record falls into. > I used: > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0)))) > this says that if the Duration value is >0 and <= 10 minutes, then it > belongs in set #1. > >10 and <= 20 is set 2, > >20 and <= 30 is set 3 > >30 and <= 60 is set 4 (you could make it >30, but it "looks cleaner" to > keep them all the same!) > > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(G2>TIME(0,30,0),4,0)))) > doesn't look quite so "nice" in my opinion! (just a LITTLE OCD) > > Next, I wanted to use the month in determining the set, so I prefixed the > formula with: > =MONTH(F2) &"."& > > giving me: > =MONTH(F2) &"."& > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0)))) > > I added two columns (A and B). > Put this in A2 and copied it down the list. > > Next, in column B I used =RAND() and copied IT down the list. > (technically, this could have been put at the end) > > Next, sort these records by the RAND column. > > Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 > etc. in the first column. > I then used vLookup to find the first record in the data that matches this > "set". > Note: since I used =month() & "." & to come up with the set names, this is > TEXT, not a number. > So, in the vlookup, you must make sure the "set" you're looking for is > TEXT. > That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in column > "L" in my case) > Excel enters them as numeric. > so in the VLookup(), I converted them to text with TEXT(). > Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$ G,3,FALSE) > > Now, in your data, there were no records for February that had a duration > of 20-30 minutes. > So no set # 2.3 > > I'm not sure what you want to do in this case. > In your explanation, you said: > "In case, any slot not available, it should pick so on." > > I have no idea what you mean by "it should pick so on". > > > *Paul* > ------------------------------ ----------- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John > Wesley*------------------------------ > ----------- > > > On Wednesday, January 27, 2016 7:25 AM, SG <sona...@gmail.com> wrote: > > > > > > Hi Experts > > 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 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 pick so on. > I really know it’s very tricky but need your help. PFA data for your > reference. > Thanks in advance > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros...@googlegroups. com. > To post to this group, send email to excel-...@googlegroups.com. > Visit this group at https://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros...@googlegroups. com. > To post to this group, send email to excel-...@googlegroups.com. > Visit this group at https://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > > > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
random_sample.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
