Solved Randomly select % of each user's entries

October 12, 2018 at 12:37:36
Specs: Windows 10
I am trying to determine how to randomly select 25% of each user's volume to include in a review. My table currently shows a unique ID column (like a ticket number) and a user name column. Data is pulled weekly to show how many tickets were completed by each user. I need a way to randomly select 25% of each user's volume to review. Last week, there were a total of 63 tickets completed by 10 people, but each person had differing volume. I would like the end result to be a list grouped by user of the ticket numbers that would constitute 25% of each person's volume for the week. I've tried to do this both with formulas and vba and can't get it pulled together. Can anyone help?

See More: Randomly select % of each users entries

Reply ↓  Report •

✔ Best Answer
October 17, 2018 at 19:00:20
Give this a try.

The following code should create the C1:H5 table shown below from the data in Columns A:B

Be careful: As written, each time the code runs it clears the previous table by clearing all cells from C1 through the end of the sheet. That's easier to code than trying to determine the size of the output table which could change based on the input data. However, if the clearing of the table needs to be more surgical, provide some more details and I'll see what I can do.

The 25% value is rounded up to the whole number so that you are always getting at least 25%.

Note: Even though the names in Column B are sorted/grouped in this example, they do not have to be. The code should work with the names in any order, i.e. grouped, ungrouped, sorted, not sorted. However, they do need to be in contiguous cells.

Let me know what you think.

	  A	  B	  C	      D	            E	          F               G	           H
1	Tkt #	Name	Name	Total Tickets	RoundUp 25%	Ticket #1	Ticket #2	Ticket #3
2	21.02	Ava	Ava	      7	            2	          24.9	          21.02	
3	66.47	Ava	Emma	      4	            1	          30.91		
4	17.53	Ava	Isabella      5	            2	           3.72	          25.1	
5	38.35	Ava	James	     10	            3	           6.29	          41.7	          19.94
6	24.9	Ava						
7	66.5	Ava						
8	89.2	Ava						
9	42.66	Emma						
10	30.91	Emma						
11	32.88	Emma						
12	71.17	Emma						
13	25.1	Isabella						
14	33.44	Isabella						
15	42.3	Isabella						
16	2.69	Isabella						
17	3.72	Isabella						
18	16.12	James						
19	30.55	James						
20	19.94	James						
21	6.29	James						
22	87.52	James						
23	25.89	James						
24	46.2	James						
25	84.34	James						
26	64.73	James						
27	41.7	James	



Option Explicit
Sub Random25ByName()
Randomize 'Initialize Random number seed
Dim myTickets() As Integer    ' Declare dynamic array.
Dim nameList, nameCount, uName, numName, percName, nxtTicket
Dim nxtRnd, chkRnd, dstCol, copyTicket, curName, nxtLabel, tickCount

'Clear table range (Clear all cells form C1 to end of sheet)
   Range(Cells(1, 3), Cells(Rows.Count, Columns.Count)).ClearContents

'Create list of Unique Names In Column C
   nameList = Cells(Rows.Count, "B").End(xlUp).Row
    Range("B1:B2" & nameList).AdvancedFilter Action:=xlFilterCopy, _
          CopyToRange:=Range("C1"), Unique:=True

'Determine total number of names in Column B
   nameCount = Application.WorksheetFunction.CountA(Range("C:C"))

'Loop through names
    For Each uName In Range("C2:C" & nameCount)
    
'Determine 25% of number of current name, Roundup to whole number
       numName = Application.WorksheetFunction.CountIf(Range("B:B"), uName)
       percName = Application.WorksheetFunction.RoundUp(numName * 0.25, 0)
       
'Allocate elements in Array
ReDim myTickets(percName)
    
'Create Random numbers, check for duplicates, fill array
     For nxtTicket = 1 To percName
getNew:
      nxtRnd = Int((nameList) * Rnd + 1)
       If Cells(nxtRnd, "B") <> uName Then GoTo getNew
        For chkRnd = 1 To nxtTicket
         If myTickets(chkRnd) = nxtRnd Then GoTo getNew
        Next
      myTickets(nxtTicket) = nxtRnd
     Next
     
'Loop through Array, copying data to Columns D and onward
  dstCol = 5
      For copyTicket = 1 To percName
        dstCol = dstCol + 1
          With Range("C2:C" & nameCount)
            Set curName = .Find(uName, lookat:=xlWhole)
          End With
            Cells(1, 4) = "Total Tickets"
            Cells(1, 5) = "RoundUp 25%"
            Cells(curName.Row, 4) = numName
            Cells(curName.Row, 5) = percName
            Sheets(1).Cells(myTickets(copyTicket), 1).Copy _
              Destination:=Sheets(1).Cells(curName.Row, dstCol)
      Next
    Next
    
'Add column labels
      For nxtLabel = 6 To Columns.Count
         tickCount = Application.WorksheetFunction.CountA(Columns(nxtLabel))
           If tickCount <> 0 Then
             Cells(1, nxtLabel) = "Ticket #" & nxtLabel - 5
           
           Else: Exit For
           End If
      Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
October 13, 2018 at 10:44:27
With only 63 tickets and 10 people, that is a rather small sample
with an average of 6 tickets each.
25% of 6 is 1.5. So we round up to 2

Even if someone has 12 tickets, 25% only comes up to 3.

I'm not sure you really need to use any formula to get your random result.

That being said, I have an idea on how it might work and will post something later today.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
October 13, 2018 at 12:44:50
With your data like this:

Column A: list of Employee Nmaes and they must be Sorted.
(I have used only 4 names. Use all 10 names on your sheet.)
Column B: Ticket numbers associated with the emloyee.

       A         B            C        D      E       F       G       H
  1) Name      Tkt #       Name List  Total  25%     1st     2nd     3rd 
  2) Ava       21.02       Ava         7      2     38.35   24.90   
  3) Ava       66.47       Emma        4      1     30.91       
  4) Ava       17.53       Isabella    5      1     33.44       
  5) Ava       38.35       James       10     3     41.70   25.89   41.70
  6) Ava       24.90              
  7) Ava       66.50                  
  8) Ava       89.20            
  9) Emma      42.66                 
 10) Emma      30.91                        
 11) Emma      32.88             
 12) Emma      71.17                           
 13) Isabella  25.10                           
 14) Isabella  33.44                           
 15) Isabella  42.30                           
 16) Isabella  2.69                            
 17) Isabella  3.72                            
 18) James     16.12                           
 19) James     30.55                           
 20) James     19.94                           
 21) James     6.29                            
 22) James     87.52                           
 23) James     25.89                           
 24) James     46.20                           
 25) James     84.34                           
 26) James     64.73                           
 27) James     41.70                           

Column C: Employee Names
Column D: We use a COUNTIF() formula to get the total number of tickets associated with each employee

=COUNTIF($A$2:$A$64,C2)

Drag the formula down for your ten names.

Column E: We use an IF() function with a ROUND() function to get the 25%

=IF(D2<4,1,ROUND(D2/4,0))

If the employee has 4 or less tickets, we simply use 1
If the employee has more than 4 we divide their total by 4 and round the answer to zero places, which usually will round up to the next highest number.

Column F,G,H: We now know how many tickets needed to be picked for 25%, to make a random selection we use the RANDBETWEEN() function with the INDEX() function to pick random rows within the appropriate range.

So for Employee Ava, we have:

=INDEX($A$2:$B$8,RANDBETWEEN(1,ROWS($A$2:$B$8)),2)

Note we only use the range of cells associated with her name, IE cells A2 thru B8, the reason the names list must be sorted.

For employee Emma, we have

=INDEX($A$9:$B$12,RANDBETWEEN(1,ROWS($A$9:$B$12)),2)

Note we only use the range of cells associated with her name, IE cells A9 thru B12

Now drag the formula to the right for the number of columns needed for 25%,
Ava needs 2 - columns F & G
Emma needs 1 - columns F
Isabella needs 1 - columns F
James need 3 - columns F & G & H

There is a gotcha, RANDBETWEEN() is volital, so each time the sheet recalculates
you will get new numbers and since we do not check for duplicates you may get the
same number repeated. Simplest solution is to recalculate and get new numbers.

Also, you could probably combine the formulas in columns D & E
into something like:

=IF(COUNTIF($A$2:$A$64,C2)<4,1,ROUND(COUNTIF($A$2:$A$64,C2)/4,0))

It's not the most elegant solution, but seems to work for getting your 25% random selection.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#3
October 14, 2018 at 08:33:19
You said that you tried VBA, so I'm going to refer you to a thread where I answered a similar question. Since I don't know your level of VBA knowledge, I don't know if you will be able to adapt this solution for your needs without some assistance.

Without knowing more details about your data layout, I wouldn't be able to modify the code at this time, so take a look and let me know if I can help further.

In that thread the requirement was to extract a random 20% of all rows with data on a given sheet. Your requirement is one, maybe two, levels deeper, but the concept/method would be the same. Instead of a percentage sampling of all rows, you need percentage samplings of multiple groups of rows with each group being based on a specific userid. That's basically the same thing, just repeated multiple times.

The concept is to first determine how rows are required to reach the desired percentage, e.g. 10 rows. The code then creates an array of 10 randomly selected rows, looping through the array each time a new random row is added to make sure that it isn't already in the array. Once the array of 10 unique rows is built, the code simply loops through the array copying the specified rows.

Let me know if you have any questions.

https://www.computing.net/answers/o...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
October 16, 2018 at 06:00:43
Mike, your solution is close to what I need, and would work perfectly if I were the one who would be doing the sampling on a normal basis. However, I am building this for someone with limited Excel knowledge, so I'm looking for something I could build in that would eliminate tasks like ordering the rows by person or updating ranges, as the data will be repopulated each week and would require the person working with the sheet to update the ranges based on the data for each person each week. Is there a way to build something that would search for the name of the person and then randomize based on the number needed?

Reply ↓  Report •

#5
October 16, 2018 at 06:07:45
DerbyDad03 - my VBA knowledge is limited unfortunately. I've done minimal work with it. My data is lined up similarly to what Mike shows above - the ticket numbers are in column A and the handlers' names are in column B. The table is built to be repopulated each week as new data is available. There is a possibility that new handlers will at some point need to be added to the list for review.

Reply ↓  Report •

#6
October 16, 2018 at 07:28:36
Using Mike's example (after swapping the columns) I'll try to modify the code from the other thread. Can't work on it right now, but I'll try to get to it later today.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
October 17, 2018 at 19:00:20
✔ Best Answer
Give this a try.

The following code should create the C1:H5 table shown below from the data in Columns A:B

Be careful: As written, each time the code runs it clears the previous table by clearing all cells from C1 through the end of the sheet. That's easier to code than trying to determine the size of the output table which could change based on the input data. However, if the clearing of the table needs to be more surgical, provide some more details and I'll see what I can do.

The 25% value is rounded up to the whole number so that you are always getting at least 25%.

Note: Even though the names in Column B are sorted/grouped in this example, they do not have to be. The code should work with the names in any order, i.e. grouped, ungrouped, sorted, not sorted. However, they do need to be in contiguous cells.

Let me know what you think.

	  A	  B	  C	      D	            E	          F               G	           H
1	Tkt #	Name	Name	Total Tickets	RoundUp 25%	Ticket #1	Ticket #2	Ticket #3
2	21.02	Ava	Ava	      7	            2	          24.9	          21.02	
3	66.47	Ava	Emma	      4	            1	          30.91		
4	17.53	Ava	Isabella      5	            2	           3.72	          25.1	
5	38.35	Ava	James	     10	            3	           6.29	          41.7	          19.94
6	24.9	Ava						
7	66.5	Ava						
8	89.2	Ava						
9	42.66	Emma						
10	30.91	Emma						
11	32.88	Emma						
12	71.17	Emma						
13	25.1	Isabella						
14	33.44	Isabella						
15	42.3	Isabella						
16	2.69	Isabella						
17	3.72	Isabella						
18	16.12	James						
19	30.55	James						
20	19.94	James						
21	6.29	James						
22	87.52	James						
23	25.89	James						
24	46.2	James						
25	84.34	James						
26	64.73	James						
27	41.7	James	



Option Explicit
Sub Random25ByName()
Randomize 'Initialize Random number seed
Dim myTickets() As Integer    ' Declare dynamic array.
Dim nameList, nameCount, uName, numName, percName, nxtTicket
Dim nxtRnd, chkRnd, dstCol, copyTicket, curName, nxtLabel, tickCount

'Clear table range (Clear all cells form C1 to end of sheet)
   Range(Cells(1, 3), Cells(Rows.Count, Columns.Count)).ClearContents

'Create list of Unique Names In Column C
   nameList = Cells(Rows.Count, "B").End(xlUp).Row
    Range("B1:B2" & nameList).AdvancedFilter Action:=xlFilterCopy, _
          CopyToRange:=Range("C1"), Unique:=True

'Determine total number of names in Column B
   nameCount = Application.WorksheetFunction.CountA(Range("C:C"))

'Loop through names
    For Each uName In Range("C2:C" & nameCount)
    
'Determine 25% of number of current name, Roundup to whole number
       numName = Application.WorksheetFunction.CountIf(Range("B:B"), uName)
       percName = Application.WorksheetFunction.RoundUp(numName * 0.25, 0)
       
'Allocate elements in Array
ReDim myTickets(percName)
    
'Create Random numbers, check for duplicates, fill array
     For nxtTicket = 1 To percName
getNew:
      nxtRnd = Int((nameList) * Rnd + 1)
       If Cells(nxtRnd, "B") <> uName Then GoTo getNew
        For chkRnd = 1 To nxtTicket
         If myTickets(chkRnd) = nxtRnd Then GoTo getNew
        Next
      myTickets(nxtTicket) = nxtRnd
     Next
     
'Loop through Array, copying data to Columns D and onward
  dstCol = 5
      For copyTicket = 1 To percName
        dstCol = dstCol + 1
          With Range("C2:C" & nameCount)
            Set curName = .Find(uName, lookat:=xlWhole)
          End With
            Cells(1, 4) = "Total Tickets"
            Cells(1, 5) = "RoundUp 25%"
            Cells(curName.Row, 4) = numName
            Cells(curName.Row, 5) = percName
            Sheets(1).Cells(myTickets(copyTicket), 1).Copy _
              Destination:=Sheets(1).Cells(curName.Row, dstCol)
      Next
    Next
    
'Add column labels
      For nxtLabel = 6 To Columns.Count
         tickCount = Application.WorksheetFunction.CountA(Columns(nxtLabel))
           If tickCount <> 0 Then
             Cells(1, nxtLabel) = "Ticket #" & nxtLabel - 5
           
           Else: Exit For
           End If
      Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#8
October 18, 2018 at 06:07:14
DerbyDad03 - that code worked perfectly! I added a button so my reviewer can easily refresh, and we should be good to go! Thank you so much for your help!

Reply ↓  Report •

Ask Question