Ticket Quality Assurance Randomizer with no duplicates

[mttkauffman]

After many Google searches and trail & errors I am stumped.

Working in Excel 2010 but have access to Excel 2007 as well (if it would matter)

I work at a help desk where we submit tickets for each call we take. We have a new team that we are needing to set up Quality Assurance where we will be checking 10 tickets per week. We currently have a report that lists out the ticket numbers and we are needing to randomly pick 10 tickets from that list without duplicates.

Right now we have the tickets in A4:A100 and the ticket numbers are in a INC123456 format.

We want the random tickets to be in A1:J1

Any ideas on how to do this where we would not get duplicates. Management came up with

=INDEX(A4:A100,RANDBETWEEN(1,ROWS(A4:A100)-COUNTBLANK(A4:A100)),1) with conditional formatting to show any duplicates and they hit F9 until no duplicates are given and would really like to just past in the ticket numbers and have it give 10 random ticket numbers from the list

Any help would be great.

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[xladept]

Hi mtt,

Try this:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

[xlbiznes]

Hi,

Awesome logic AlphaFrog.

[mttkauffman]

That worked amazingly well!! Thank you!!!!!!!!