Macro to duplicate a name that has been randomly generated IF cell value has a number

[dallekvist]

Hi guys and gals!

I've been using this forum a lot to get inspiration to build a spreadsheet for work that is both functional and simple to use. I realized that "simple" features in Excel often have complex functions and/or codes (VBA) that you have to master before enjoying your hard work.

I'll try to explain to the best of my abilities with a little background information, so here goes.

I've built this caseworker assignment sheet (attached) that randomly selects a caseworker to cases by using one of the macros "Assign CW1, Assign CW1 Uneven Weeks, Assign CW2, Assign CW2 Uneven Weeks." You'll find the buttons in the "Assign" sheet. (Column M2)

Here is where it gets a little tricky.

Some of these cases are "bundles," (See column H) which means they should be processed by the same caseworker (CW1 and CW2) for easy processing. I have assigned a number to those who need to be processed together in column H.

Based on the numbers listed in that column, I want to choose a random CW1 and random CW 2 - just like I did the first time when I assigned them - to process those cases; HOWEVER, it has to be the same two (CW1 and CW2) handling the entire bundle, e.i, H8 and H9 both have the number "1" which should tell the macro/formula that whoever caseworker (CW1 and CW2) is generated for I8 and I9, they have to be the same because they are being treated as a bundle. In the spreadsheet you can see that Daniel is assigned as CW1 and Charlotte is assigned CW2 (I8,I9 and J8,J9 respectively). I manually changed that from the dropdown list to show you what I mean.

So, I would like to build a macro that can tell which numbers belong together (1,1 and 2,2,2, and 3,3,3,3 etc.) and assign the same CW1 and CW2 for the entire bundle. The assigned caseworkers should be random so the same two caseworkers aren't doing all the work.

Those cells in column "H" that have been left blank can be assigned to any caseworker since they are individual cases. You can easily select the two or more caseworkers manually from the dropdown list, but I was hoping it could be done automatically since some days we have over 1000 cases.

Is this possible? Thank you so much :D

I can post a link to the spreadsheet upon request. Just let me know in the comment box below, thank you!

 

[kweaver]

You cannot attach workbooks, but you can capture portions of a sheet or sheets using XL2BB. That is very helpful to someone trying to solve your problem.

 

[dallekvist]

You cannot attach workbooks, but you can capture portions of a sheet or sheets using XL2BB. That is very helpful to someone trying to solve your problem.

Thank you Kweaver.

I was unable to do anything with the XL2BB since I'm working on a Mac and had errors come up everytime I tried. I've attached an image og the workbook and below I'm pasting the code I'm already using for assigning caseworkers in case something can be weaved in that macro already

Option Explicit

Sub AssignCaseworkerCW1()

Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String 'Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long 'Variable to be used when entering names onto worksheet
Dim ArI As Byte 'Variable to increment through array indexes

Application.ScreenUpdating = True

HowMany = Range("M7").Value
CellsOut = 8

ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Sheets("Code").Range("A:A")) - 1 'Find how many names in the list
i = 1

Do While i <= HowMany
RandomNo:
    RandomNumber = Application.RandBetween(2, NoOfNames)
    'Check to see if the name has already been picked
    For ArI = LBound(Names) To UBound(Names)
       
    Next ArI
    Names(i) = Sheets("Code").Cells(RandomNumber, 1).Value 'Assign random name to the array
    i = i + 1
Loop

'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)

    Cells(CellsOut, 9) = Names(ArI)
    CellsOut = CellsOut + 1

Next ArI

Application.ScreenUpdating = True

End Sub