Trying to modify existing macro to select randomly, versus in alphabetical order...

[SeanEboy]

Hello all!
In the past, I've had great success with a macro the user Kpark had whipped up for me... long story short, I want to add a feature to the macro, if you will. Basically, we have a list of 26k leads, however they're only 2300 locations. I do not want to send to all 26k leads, but a selected matrix (if 20, send 5, and so on). However, I want these leads to be selected at random, versus simply being the first x alphabetically. I will post the existing macro below for reference, and I'd appreciate if anyone could chime in on it.

So again, I'm hoping to have the macro find the amount of leads per location, then randomly select the desired amount as per the table in sheet 3. Sheet 3 just has 2 columns A- is amount of hits per location, and B- is amount of catalogs to send. I.e. if 1, send 1, if 2, send 1, if 3, send 2, if 4, send 2, etc. Here is a sample of the data: https://docs.google.com/spreadsheets...VDE/edit#gid=0

Thanks!

Sub kpark()
Dim Msg As Integer
Msg = MsgBox("Have you sorted the mailing list?", vbYesNo)
If Msg = 7 Then
MsgBox ("Please sort the list.")
Exit Sub
End If

Application.ScreenUpdating = False
Dim LR1 As Long, i1 As Long, i2 As Long, i4 As Long, count As Long, catNum As Long

count = 1
catNum = 1
i2 = 3
i4 = 2
LR1 = Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
Sheets("Sheet1").Range("A2:H2").Copy Sheets("Sheet2").Range("A2")

For i1 = 3 To LR1
If Sheets("Sheet1").Range("D" & i1).Value = Sheets("Sheet1").Range("D" & i1 - 1).Value Then
count = count + 1
If Sheets("Sheet3").Range("B" & count + 1).Value > catNum Then
catNum = catNum + 1
Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet2").Range("A" & i2)
i2 = i2 + 1
Else
Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet4").Range("A" & i4)
i4 = i4 + 1
End If
Else
count = 1
catNum = 1
Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet2").Range("A" & i2)
i2 = i2 + 1
End If

Next i1

Application.ScreenUpdating = True

End Sub

[jdc2000]

Question:

What column(s) are you sorting the list on? I assume that Sheet1 is the list that needs to be sorted.

Once I have this information, I can change the message box to simply ask if you want the list sorted, and, if yes, sort it and proceed with the macro, selecting a random amount of leads based on your table in Sheet3.

[SeanEboy]

Question:

What column(s) are you sorting the list on? I assume that Sheet1 is the list that needs to be sorted.

Once I have this information, I can change the message box to simply ask if you want the list sorted, and, if yes, sort it and proceed with the macro, selecting a random amount of leads based on your table in Sheet3.

Thanks JDC! Yes, we're sorting on column D, or INST (institution name). I think you know what's up, however - Again, the macro gets us the amount of leads, but they're always the first few in the alphabetical order. I'd love to get random ones from across the selection.

[jdc2000]

I have some of the modifications ready. I will need to download your sample data file to finish and test them. I assume that it is available to anyone. I cannot do this from my work computer, so that will have to wait until later tonight.

[SeanEboy]

I have some of the modifications ready. I will need to download your sample data file to finish and test them. I assume that it is available to anyone. I cannot do this from my work computer, so that will have to wait until later tonight.

Thanks anyway, however I just realized that the names are not sorted alphabetically! Go figure.. haha.

[jdc2000]

I not sure that has any bearing on the issue, if you just want a random selection of a specified number of records from each Location group. As long as the list can be sorted by Location, your idea should work. I may end up sorting the list by Location and then Name, which is not a problem.

[jdc2000]

The link you posted returns a 404 error. Zip a copy of the sample file and attach it here.