Assign a List of numbers to a List of names

ifathy · Jul 14th 2017

Hi,

I have a long list of customers (above 1000), includes 3 columns

- Customer name
- Customer Number
- Segment

I need to associate each number to the relevant customer name and Segment, so when i add a new entry by Customer name that it automatically add the Name and the Segment.

Thanks,
Islam

sktneer · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Welcome to the forum.

Please attach a sample workbook to visualize the data layout.

[sw]*[/sw]

ifathy · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Please find attached file

sktneer · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Thanks for the file.
So what do you want to achieve here?

Do you want to enter a Customer# in column A and want the name and the segment to be filled automatically if the Customer# already exists in the list above?
e.g.
If you input "8975-AJ" again in A1075, you want B1075 to be filled with "DORAT AL MAKASEB F" and C1075 to be filled with "CONSTRUCTION"?

ifathy · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Thanks for the prompt reply.

Yes this is what exactly what i am looking for. If you please explain how to do it in details i will appreciate that.

Thanks

sktneer · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Please find the attached. It is a macro enabled workbook and the code is on Sheet1 Module.

How will this work:
As soon as you input a Customer# in column A, the code will look at the existing data above and if it finds the same Customer#, it will fill the column B and C automatically.
Also if you delete a customer#, the corresponding cells in column B and C will also be deleted.

Code working behind the scene:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim Rng As Range
Dim r As Long
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        Set Rng = Range(Cells(2, 1), Cells(Target.Row - 1, 1))
        If Application.CountIf(Rng, Target.Value) > 0 Then
            r = Application.Match(Target.Value, Rng, 0)
            Target.Offset(0, 1) = Cells(r, 2)
            Target.Offset(0, 2) = Cells(r, 3)
        End If
    Else
        Target.Offset(0, 1) = ""
        Target.Offset(0, 2) = ""
    End If
    Application.EnableEvents = True
End If
End Sub

Display More

How to implement the same code to your actual workbook?

1) Copy the above code.

2) Open your original workbook.

3) Right click on the Sheet Tab --> View Code --> And paste the code into the opened code window.

4) Close the VB Editor and save your workbook as Macro-Enabled Workbook.

ifathy · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Thanks a lot,

This is what i did, i copied the code to Sheet1, but when i start entering the data it is shifted by 1 row, as follows
- If i enter 5005 (Cell A2) in cell A1075 it automatically enter "Customer name" (Cell B1 not B2) in B1075 and "Segment" (Cell C1 not C2) in C1075

I believe something need to be adjusted ?
and what if i need to use the same data in Sheet2 for example?

sktneer · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Ah... my bad.

In the following line make the change as suggested in RED.

Set Rng = Range(Cells(1, 1), Cells(Target.Row - 1, 1))

i.e. it should be 1 instead of 2 so replace 2 with 1.

ifathy · Jul 14th 2017

Re: Assign a List of numbers to a List of names

You are my hero

what if i need to use the same data in another sheet (Sheet2 for example)?

sktneer · Jul 14th 2017

Re: Assign a List of numbers to a List of names

For that requirement, place the following code on Sheet2 Module.
Refer to the attached.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
Dim r As Long
Set ws = Sheets("Sheet1")
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        If Application.CountIf(ws.Columns(1), Target.Value) > 0 Then
            r = Application.Match(Target.Value, ws.Columns(1), 0)
            Target.Offset(0, 1) = ws.Cells(r, 2)
            Target.Offset(0, 2) = ws.Cells(r, 3)
        End If
    Else
        Target.Offset(0, 1) = ""
        Target.Offset(0, 2) = ""
    End If
    Application.EnableEvents = True
End If
End Sub

Display More

ifathy · Jul 14th 2017

Re: Assign a List of numbers to a List of names

Thanks again, it works perfectly

One last question, what if i need to add more columns lets say column D and E? what should i change

sktneer · Jul 15th 2017

Re: Assign a List of numbers to a List of names

Like this...

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim Rng As Range
    Dim r As Long
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        If Target <> "" Then
            Set Rng = Range(Cells(2, 1), Cells(Target.Row - 1, 1))
            If Application.CountIf(Rng, Target.Value) > 0 Then
                r = Application.Match(Target.Value, Rng, 0)
                Target.Offset(0, 1) = Cells(r, 2)
                Target.Offset(0, 2) = Cells(r, 3)
                Target.Offset(0, 3) = Cells(r, 4)
                Target.Offset(0, 4) = Cells(r, 5)
            End If
        Else
            Target.Offset(0, 1) = ""
            Target.Offset(0, 2) = ""
            Target.Offset(0, 3) = ""
            Target.Offset(0, 4) = ""
        End If
        Application.EnableEvents = True
    End If
End Sub

Display More

ifathy · Jul 15th 2017

Re: Assign a List of numbers to a List of names

Great, got it
really appreciated

sktneer · Jul 15th 2017

Re: Assign a List of numbers to a List of names

You're welcome!
Another way to say thanks is to click on *Give Reputation link.

ifathy · Jul 15th 2017

Re: Assign a List of numbers to a List of names

Please need to ask more question, what if i need to use Column B instead of Column A, i mean if i need to put the customer name and it fill Column A and C automatically
I tried to play with it myself but didn't work

Thanks

sktneer · Jul 15th 2017

Re: Assign a List of numbers to a List of names

Sheet1 Module:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim Rng As Range
Dim r As Long
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        Set Rng = Range(Cells(1, 1), Cells(Target.Row - 1, 1))
        If Application.CountIf(Rng, Target.Value) > 0 Then
            r = Application.Match(Target.Value, Rng, 0)
            Target.Offset(0, 1) = Cells(r, 2)
            Target.Offset(0, 2) = Cells(r, 3)
        End If
    Else
        Target.Offset(0, 1) = ""
        Target.Offset(0, 2) = ""
    End If
    Application.EnableEvents = True
ElseIf Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        Set Rng = Range(Cells(1, 2), Cells(Target.Row - 1, 2))
        If Application.CountIf(Rng, Target.Value) > 0 Then
            r = Application.Match(Target.Value, Rng, 0)
            Target.Offset(0, -1) = Cells(r, 1)
            Target.Offset(0, 1) = Cells(r, 3)
        End If
    Else
        Target.Offset(0, -1) = ""
        Target.Offset(0, 1) = ""
    End If
    Application.EnableEvents = True


End If
End Sub

Display More

Sheet2 Module:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
Dim r As Long
Set ws = Sheets("Sheet1")
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        If Application.CountIf(ws.Columns(1), Target.Value) > 0 Then
            r = Application.Match(Target.Value, ws.Columns(1), 0)
            Target.Offset(0, 1) = ws.Cells(r, 2)
            Target.Offset(0, 2) = ws.Cells(r, 3)
        End If
    Else
        Target.Offset(0, 1) = ""
        Target.Offset(0, 2) = ""
    End If
    Application.EnableEvents = True
ElseIf Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        If Application.CountIf(ws.Columns(2), Target.Value) > 0 Then
            r = Application.Match(Target.Value, ws.Columns(2), 0)
            Target.Offset(0, -1) = ws.Cells(r, 1)
            Target.Offset(0, 1) = ws.Cells(r, 3)
        End If
    Else
        Target.Offset(0, -1) = ""
        Target.Offset(0, 1) = ""
    End If
    Application.EnableEvents = True


End If
End Sub

Display More

Assign a List of numbers to a List of names

Participate now!

Share