+ Reply to Thread
Results 1 to 9 of 9

Code to look up values from one column to another

  1. #1
    Forum Contributor
    Join Date
    03-12-2014
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    118

    Code to look up values from one column to another

    Hi,
    Need code/formula for following things
    I have three excel sheet in a workbook called “Leads”, “Properties” and “Brokers”.
    1) I want code/formula to look up for the mobile number column from sheet “Leads” column “Q” with sheet “Brokers” column “M”.

    If any of the number in column “M” of sheet “Brokers” is matching with number in column “Q” of sheet “Leads” then need result in adjacent cell “Column R” of sheet “Leads” as Brokers Sheet contains following numbers [(****Matching Number)]

    2) Similar code/formula to look up for the mobile number column from sheet “Properties” column “AN” with sheet “Brokers” column “M”.

    If any of the number in column “M” of sheet “Brokers” is matching with number in column “AN” of sheet “Properties” then need result in adjacent cell Column “AM” of sheet “Properties” as Brokers Sheet contains following numbers [(****Matching Number)]

    3) The above code/formula is needed to compare email for both the sheets “Leads” and “Properties” with sheet “Brokers"
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Code to look up values from one column to another

    Copy this code and paste it into a code module in your workbook, and save your file as a macro-enabled .xlsm file.

    Please Login or Register  to view this content.
    Then, in cell R2 of sheet "Leads", and in AO2 of sheet "Properties" use the first function like this:

    =BrokerPhone(Q2,Brokers!M:M)
    =BrokerPhone(AN2,Brokers!M:M)

    and in V2 and AV2 use

    =BrokerEmail(U2,Brokers!P:P)
    =BrokerEmail(AU2,Brokers!P:P)

    and copy down.
    Last edited by Bernie Deitrick; 12-15-2014 at 10:59 AM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    03-12-2014
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: Code to look up values from one column to another

    Gr8. thanks a ton Bernie

  4. #4
    Forum Contributor
    Join Date
    03-12-2014
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: Code to look up values from one column to another

    Hi,

    Can you please provide me vba code for the same instead of function. Also need vba to compare data among multiple workbooks instead of worksheets.

    I am new to excel. please give me code with remarks to change workbook name, sheet name,, cell or column reference. I need code to compare from multiple workbook. So please give option in code to add additional workbook name. Please help

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Code to look up values from one column to another

    You can use the same function in VBA like this for the first set of formulas:

    Worksheets("Leads").Range("R2").Value = BrokerPhone(Worksheets("Leads").Range("Q2"),Worksheets("Brokers").Range("M:M"))
    Worksheets("Properties").Range("AO2").Value = BrokerPhone(Worksheets("Properties").Range("AN2"),Worksheets("Brokers").Range("M:M"))

  6. #6
    Forum Contributor
    Join Date
    03-12-2014
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: Code to look up values from one column to another

    Hi Bernie,

    Need your help again in the same code.

    Can you help me with the code to check duplicate mobile number and email id if it is seperated by "/" or "," or ";"

    E.g


    If I want to check the mobile number 998888855 in the other column where number contains with multiple number like 998888855/999854511
    and email id [email protected] in the other column where email contains with multiple email id in one cell like [email protected]/[email protected]

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Code to look up values from one column to another

    These versions should work with / ; or , delimiters:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    03-12-2014
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: Code to look up values from one column to another

    Hi Bernie,

    Just one last question. From the above code how to change the column number and sheet name?

    Suppose if I want to cross check the duplicates with some other sheet and from other column. How to change the cord accordingly.

    Also How to compare duplicate with more than 3 or 4 sheets.

    Can you provide code with step by step note. please help, I am new to excel

  9. #9
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Code to look up values from one column to another

    You don't need to change the code - just change how you are using it, by changing the sheet names and ranges of interest. For example, change any of the bolded sections to other sheet names or ranges:

    Worksheets("Leads").Range("R2").Value = BrokerPhone(Worksheets("Leads").Range("Q2"),Worksheets("Brokers").Range("M:M"))

    And though you haven't been using it, you can specify the workbook, too:

    Workbooks("File name.xlsx").Worksheets("Leads").Range("R2").Value = BrokerPhone(Workbooks("Other File name.xlsx").Worksheets("Leads").Range("Q2"),Workbooks("Other other File name.xlsx").Worksheets("Brokers").Range("M:M"))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] the code is finding repeated values but i need to search values from E column to D
    By baig123 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-14-2014, 01:33 AM
  2. [SOLVED] Select Case Code for Column A Values to Populate Results in Column B in Same Row
    By excelforumkeys in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-28-2014, 05:22 PM
  3. [SOLVED] need vba code to compare two column get result of common values of both column
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-12-2013, 05:42 AM
  4. VBA code to sum up values in column B depending on column A
    By Dan27 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-29-2013, 05:02 AM
  5. VBA code to Subtract values in Column F with Column E if found non-blank
    By hydz1213 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2013, 04:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1