I think I better understand what your data means.You're wanting a random sample of (4) records for each of the names in the "Name" column.and you're wanting the (4) samples to be in each if (4) time ranges. The attached file takes care of THAT, but your additional requirement, that of what to do if you don't have a match within the time rangemay be more problematic. An attempt to create a "flag" to show when a record is selected,then, when no entry exists, choose from those that were not selected.Causes a circular reference. I think the only way to get past this is to write a VBA macro that loads the data into an arrayand processes the array. But before I try something like that: Is this the way the data is "presented" to you?is there any "unique" identifier for the records?
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 Monday, February 1, 2016 8:33 AM, SG <sonal...@gmail.com> wrote:
Paul, apologies for bothering you so much & unable to interpret the
expectation. Please refer sheet 2 in attached file for results per agent.Also,
I have taken duration as example of selecting sample.
On Monday, February 1, 2016 at 6:37:46 PM UTC+5:30, Paul Schreiner wrote:
I'm not sure what you are expecting, or how this does not meet your
expectations.
For January, you have (9) entries in the 0-10 minute range.So each of your (4)
agents will get one of the samples.
But you only have (3) entries in the 10-20 minute range.So, the 4th agent does
not receive an entry.
The 20-30 minute range only has ONE entry, so only the first agent receives an
entry.
in the "over 30" range, there are two entries, so only the first two agents
receive entries.
That all seems to work.
You said: "if less than 4, still it would come with number of calls it has"I
have no idea what that means.does that mean that if there are not sufficient
records for that time period, you want to take an entry from another?
If you could:Take the records you've provided for Januaryand show me what you
would EXPECT the (4) agents to show.(and why?)
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 Monday, February 1, 2016 5:34 AM, SG <sona...@gmail.com> wrote:
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
RegardsSG
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> 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 asthere are 2-3
conditions on basis of which sampling should be done. It should be done for 4
calls per month per type (column Afor 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...@ 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.
--
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_02-01.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12
