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?

✔ 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 least25%.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 JamesOption 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

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.

MIKE

message edited by mmcconaghy

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.70Column 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 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 & HThere 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

message edited by mmcconaghy

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

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?

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.

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

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 least25%.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 JamesOption 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

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!

Ask Your Question

Weekly Poll