Replace ID Number with Customer Name using two arrays

DynamiteHack

Board Regular
Joined
Jan 14, 2012
Messages
60
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!! :rolleyes:

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

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Easiest option would be to add a new column to your job tables with a vlookup, or index/match to the customer table
 
Upvote 0
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...
 
Upvote 0
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/
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
You're welcome, if you need any help just shout.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top