# 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

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:BBe 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 ```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 2Even 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.MIKEmessage edited by mmcconaghy

#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 NamesColumn 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 1If 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 B12Now 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 recalculatesyou will get new numbers and since we do not check for duplicates you may get thesame number repeated. Simplest solution is to recalculate and get new numbers.Also, you could probably combine the formulas in columns D & Einto 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.MIKEmessage edited by mmcconaghy

#3
October 14, 2018 at 08:33:19

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?

#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.

#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.

 Give this a try.The following code should create the C1:H5 table shown below from the data in Columns A:BBe 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 ```message edited by DerbyDad03