Replace ID Number with Customer Name using two arrays

[DynamiteHack]

I am using an array to populate a ListBox. I understand how to create an array and use it to populate a listbox with items from the Jobs table. I also have a customer table. The Jobs table contains the CustID number. How would I approach replacing the CustID number with their name from the Customer table?



Not necessarily looking for actual code, just a strategy. I have been stuck on this since yesterday and I am googled out!!

Thanks in advance!
https://cdn1.imggmi.com/uploads/2019/3/19/19985b09e335db4eab12c7d32d6bbcd8-full.png

 

[steve the fish]

That would depend on what is in the two tables.

 

[Fluff]

Easiest option would be to add a new column to your job tables with a vlookup, or index/match to the customer table

 

[DynamiteHack]

Thanks for the reply, Steve. Did the image not post correctly?

 

[DynamiteHack]

Thank you for the reply, Fluff!
I was trying to keep my tables in a relational database style and not have repeating records. Seemed I'd be able to create two arrays and do the substitution at runtime, possibly creating a third array that actually populates the listBox. Maybe it's not a good approach as I've had trouble finding such a solution online...

 

[Fluff]

You could use a dictionary, loop through the customer table adding the ID as a key & the name as the item, then loop though the array you use to populate the listbox swapping the ID for the name in the dictionary.
If you don't know of dictionaries have a look here https://excelmacromastery.com/vba-dictionary/

 

[HankJ]

Hi
You may care to give this a go.
I hope I have understood the issue correctly.
Please let me know if I haven't.

Sub Swop()
Dim Wb1 As Workbook
Set Wb1 = ThisWorkbook

Dim Sh1 As Worksheet
Set Sh1 = Wb1.Worksheets("Sheet1")

    Dim rCell As Range
    Dim rRng As Range
    Dim LastRow As Long
    LastRow = Range("B500").End(xlUp).Row      ' the number lines


    Set rRng = Sh1.Range("B2:B" & LastRow)

    For Each rCell In rRng.Rows
  
        rCell = Application.WorksheetFunction.VLookup(rCell, Sh1.Range("F:G"), 2, False)
        
      
    Next rCell

End Sub

Edit after posting: I recreated the second table somewhat further east than yours, so my F:G might not make much sense to you, sorry.
Hj

 

[steve the fish]

Heres something that could work for you. I know you said you didnt really need code but its harder to explain! Basically it puts both the Tables into separate arrays the nloops through the Jobs one changing the number to the name if it finds a match. It doesnt then do anything so its up to you how you want to use it.

Set sh = Sheets("Sheet1")

Jobs = sh.Range("Jobs")
Custs = sh.Range("Custs")

For i = LBound(Jobs) To UBound(Jobs)
    x = Application.Match(Jobs(i, 2), Application.Index(Custs, 0, 1), 0)
    If Not IsError(x) Then
        Jobs(i, 2) = Application.Index(Custs, x, 2)
    End If
Next

 

[DynamiteHack]

Thanks again, Fluff. I will look into that. I'll let you know how it goes...

 

[Fluff]

You're welcome, if you need any help just shout.