Random Details Picker VBA query

[Ben H]

Hi

I am tasked with creating a method via vba that will randomly pick staff ids and department names from a list - I have attached a sample document together with VBA code within the document.

I need to get exactly 12.5% return from each department. Using basic formulas and a pivot table I can determine what 12.5% of the each department would come to however I am unsure as to how to randomly pick exactly 12.5% of these so that the vba would split each by a random 12.5% and return precisely that number.

At present I am using the following vba ...

Sub random50()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range
Dim lr As Long
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ws1.Range("A1:A" & lr)
For i = 1 To 94
    ws2.Range("A" & i) = Application.WorksheetFunction.Index(rng, Application.WorksheetFunction.RandBetween(1, lr))
Next i
End Sub

Using this I would filter the main data sheet by department, paste that data onto sheet 1 and on sheet 2 run the random number picker - which should return exactly 12.5% of the numbers and departments with no duplicates picked at random - it doesn't do that at present. After I have done that I would copy and paste each time because I am building a complete document with 5,000 random employee ids and departments in it.

Actually it would be great to have a document that could pick 12.5% at random each department and with no duplicates and create a complete list of the 5,000 employee id's and departments with no duplicates in - instead of cutting and pasting each time to form a list of 5000 employee ids and departments.

Your advice would be welcomed.

Kind Regards

Ben Howard

 

[Luke M]

Does it have to be via VB?
I'd setup a Random column next to your raw data, and then create a PivotTable which shows the top 12.5% of items. This would create your list nicely.

In your file, I added some helper columns. First generates the random seed.
Second column gives count of items. This is to help the Pivot, since we want 12.% of each department, and not just overall. third column combines the two. I used multiple columns to help processor power. RAND function is volatile, but I dion't want to recalculate the COUNTIFs everytime, as they take awhile.

Next, I created the PivotTable to show department and ID's, and then filterd ID's by top 12.5%. The list on Pivot sheet should be what you want. Refresh the PivotTable to "redraw" a new set of numbers.

Note: To reduce file size, I had to remove all the records after row 5000 in your original raw data. You should still be able to see the general layout, and could paste/add the rows back in.

 

[Ben H]

Hi Luke

This is interesting but not what I am looking for.

I am tasked with randomly picking 5,000 employee ids and departments but the split is by department and needs to be 12.5% equal distribution across those departments - so larger departments would have a bigger headcount.

The total does need to come from the 45-50,000 employee ids and departments I sent through.

Just picking the top 12.5% from the total of all the employee id's will not work.

First i would need to know how much each departments 12.5% total would come to from the total for each department, before picking those 12.5% ids from each of those departments based on their relative sizes.

Kind Regards

Ben Howard

 

[Luke M]

HI Ben,

Not sure if you looked at the file...if you had, you'll see that what you descibe is what the Pivot is doing, ie. getting 12.5% of each department, not overall. That's why the 12.5% filter is on the ID column.

Some quick check on the file I posted:


So, CCFSDO originally had 20 people in department. 12.5% of that is 2.5. We roundup to 3, and we see that yes, the PT has 3 ID's for that department. Versus the smaller department of CEA which only had 3, so only 1 member is returned.

 

[Ben H]

Hi

Thanks I have now got it to work - just need to work my way through it.

Kind Regards

Ben Howard