Assign a List of numbers to a List of names

  • 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

  • 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"?

  • 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

  • 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:


    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.

  • 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?

  • 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.

  • 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.


  • 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

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


    Like this...


  • 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

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


    Sheet1 Module:


    Sheet2 Module:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!