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

dallekvist

New Member
Joined
Feb 2, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,575
Office Version
  1. 365
  2. 2010
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

New Member
Joined
Feb 2, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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 :)

VBA Code:
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
 

Attachments

  • Workbook.JPG
    Workbook.JPG
    255 KB · Views: 1
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top