Multi Dim array printing specific value to range depending on value in Col A

[RumSwift]

Hiya,

I'm building a roster for my business and to save time, I'm attempting to automate it. this is due to multiple rosters rebuilds taking place throughout the rostered period & I would rather not copy and paste names hundreds of times.

The Roster on Sheet "Roster" looks like this:

Line/Rotation Number	Date	Employee
1	01/03/2023
1	02/03/2023
1	03/03/2023
2	04/03/2023
2	05/03/2023
2	06/03/2023
3	07/03/2023
3	08/03/2023
3	09/03/2023

I have a multi dim array that fills from a table of active staff. Each staff member has their own unique "line" attached to them. So for example, the array looks like this:

Item One
Joe Bloggs
1

Item Two
Anita Formula
2

Item Three
Chris Swanson
3

The Array has the employee's name in position 1 and their line/rotation number is position 2.

the roster is not in a table format, it's just a range. This is due to it essentially being a template to be uploaded to a different system and requires it to just be a regular range & won't work if it's in a table. Meaning I have to find the range by getting last row. so depending on the length of the current roster, it could be A1:D400 or A1:D100.

I essentially need something that loops through the range and fills the final col (this case will be D) with the emplyee name where the line number in col A is the number in position 2 of the array. So, the above would come out looking like this:

Line Number	Date	Employee
1	01/03/2023	Joe Bloggs
1	02/03/2023	Joe Bloggs
1	03/03/2023	Joe Bloggs
2	04/03/2023	Anita Formula
2	05/03/2023	Anita Formula
2	06/03/2023	Anita Formula
3	07/03/2023	Chris Swanson
3	08/03/2023	Chris Swanson
3	09/03/2023	Chris Swanson

I've shortened the example above but the array I currently have has 32 staff in (so 32-line numbers). For some extra info, this can change each time I have to roll a new roster through. So I've already set everything up so far to be dynamically sized. f that makes a difference.

Any help is massively appreciated.

 

[Alex Blakenburg]

I have assumed the Employee Listing actually looks like the below.
Also I have used a sheet name of Employees for that sheet.
On that basis try the code below.

20230221 VBA Dict Assign Employees to Roster RumSwift.xlsm
	A
1	Joe Bloggs
2	1
3	Anita Formula
4	2
5	Chris Swanson
6	3
Employees

Sub AllocateNamesToRoster()

    Dim shtRoster As Worksheet, shtEmpLst As Worksheet
    Dim rstrRng As Range, empRng As Range
    Dim rstrLastRow As Long, empLastRow As Long
    Dim rstrArr As Variant, empArr As Variant
    Dim i As Long
    
    Set shtRoster = Worksheets("Roster")
    Set shtEmpLst = Worksheets("Employees")         '<-- Change to actual sheet name
    
    With shtRoster
        rstrLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rstrRng = .Range(.Cells(2, "A"), .Cells(rstrLastRow, "C"))
        rstrArr = rstrRng
    End With

    With shtEmpLst
        empLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        Set empRng = .Range(.Cells(1, "A"), .Cells(empLastRow, "C"))
        empArr = empRng
    End With
    
    Dim dictEmp As Object, dictKey As String
    
    ' Load employee list range into Dictionary
    Set dictEmp = CreateObject("Scripting.dictionary")
    For i = 1 To UBound(empArr) Step 2
        dictKey = empArr(i + 1, 1)              ' Rotation no on 2nd Row
        If Not dictEmp.exists(dictKey) Then
            dictEmp(dictKey) = empArr(i, 1)
        End If
    Next i

    ' Load names into Roster Array
    For i = 1 To UBound(rstrArr)
        dictKey = rstrArr(i, 1)
        If dictEmp.exists(dictKey) Then
            rstrArr(i, 3) = dictEmp(dictKey)
        End If
    Next i
    
    ' Write back Employee Names
    rstrRng.Columns(3).Value = Application.Index(rstrArr, 0, 3)

End Sub

 

[RumSwift]

Spot on! Made it work with a few tweaks. thanks so much

 

[Alex Blakenburg]

You're welcome. Glad I could help.