+ Reply to Thread
Results 1 to 5 of 5

How to lookup duplicate values between sheets and replace with value from another column

  1. #1
    Registered User
    Join Date
    12-22-2008
    Location
    Missouri
    Posts
    39

    How to lookup duplicate values between sheets and replace with value from another column

    See attached sheet:

    Sheet A has a list of ID numbers
    Sheet B, Column A has a list of ID numbers that were considered temporary
    Sheet B, Column B has the list of ID numbers that the temporary ID numbers need to be changed to
    Not all numbers in Sheet B are in Sheet A.
    If not in sheet A I don't want them to transfer.
    Thanks ahead of time.

    ID Test.xlsx
    Last edited by ladydubs; 08-26-2013 at 01:00 PM.

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: How to lookup duplicate values between sheets and replace with value from another colu

    In Sheet A, Cell B2, use:
    =VLOOKUP(A2,'Sheet B'!$A$2:$B$100,2,FALSE)
    Copy down.

    That will highlight the IDs in Sheet A that need to be replaced by the new Values from Column B in Sheet B.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: How to lookup duplicate values between sheets and replace with value from another colu

    In Cell B2 of Sheet A

    =IFERROR(INDEX('Sheet B'!B:B,MATCH('Sheet A'!A2,'Sheet B'!A:A,0)),A2)

    Copy down

    Copy column B -- Paste Special values

    Column B of Sheet A now has updated IDs
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: How to lookup duplicate values between sheets and replace with value from another colu

    Hi

    In C2 of Sheet2 enter the following
    =IF(COUNTIF('Sheet A'!A:A,'Sheet B'!A2),'Sheet B'!B2,"")
    and copy down

    If this does achieve what you are looking for, then I would copy Column C and Paste Values to say column F.
    Then with that range selected, choose Data>Remove Duplicates and all of your blanks will be removed, and you data in the same Order.
    Alternatively, if Order doesn't matter you could just Sort column F and all of the Blanks would fall to the end.
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  5. #5
    Registered User
    Join Date
    12-22-2008
    Location
    Missouri
    Posts
    39

    Re: How to lookup duplicate values between sheets and replace with value from another colu

    Thanks all, I got the results I wanted on all of your replies.

    This saved me a ton of headache.

+ 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] Vlook up when duplicate values in the array table and lookup column
    By vinodt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2012, 10:28 PM
  2. [SOLVED] identify unique ID between 2 sheets, auto replace cell values between sheets in diff colum
    By DT123456 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2012, 08:25 AM
  3. Seach two ranges for a duplicate values and replace
    By Ricker090 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2011, 04:58 PM
  4. Preventing from entering duplicate values in the same column across sheets
    By coach_tyro in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-12-2008, 06:43 PM
  5. Find and Replace values from multiple lookup values
    By Gregula82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2007, 03:12 PM

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