+ Reply to Thread
Results 1 to 4 of 4

identify unique ID between 2 sheets, auto replace cell values between sheets in diff colum

  1. #1
    Registered User
    Join Date
    07-12-2012
    Location
    aus
    MS-Off Ver
    Excel 2003
    Posts
    2

    identify unique ID between 2 sheets, auto replace cell values between sheets in diff colum

    Hi All,

    Am new here, have been reading alot here and most of what i've done is find snippets of everything else on this forum and adapt it to what i need, so thanks its been very helpful to date

    I am particularly stumped on a problem. I have two spreadsheets (S1, S2) with the same uniqueID number in column B. Each sheet will have thousands of rows, with up to 20 columns. What I need to do is have a macro that compares columns B in both sheets S1 and S2 and if values in cells in column B match, copy the value of column R in S2 , and replace this value INTO column K in S1.

    For Example:

    In S1, cell B5000 = 123, cell K5000 = 1.0

    In S2, cell B400 = 123, cell R400 = 5.0

    When macro is run it 'scans' the above two rows in separate sheets , detects they have the same unique ID, then replaces(or copy/pastes) cell K5000 (1.0) with the values from Sheet S2, cell B19 (5.0).

    In other words it scans the 'rows' and column B from S2, and if there is a match to column B in S1 it replaces the corresponding cell value in R400 of S2 into K5000 of S1. By row i mean the corresponding 'cell' in that particular row, based on the column...sorry i am not sure how else to put it. The final result should look like:

    S1, cell B5000 = 123, cell K5000 = 5.0
    S2, cell B400= 123 , cell R400 = 5.0 (no change).

    I have had a look at afew of the compare/replace threads though seem unable to adapt them to what i need. Any help would be great, thanks!

    Cheers.

  2. #2
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: identify unique ID between 2 sheets, auto replace cell values between sheets in diff c

    Backup your data.
    Place the following code in a standard module.
    Run: CopyR2K

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-12-2012
    Location
    aus
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: identify unique ID between 2 sheets, auto replace cell values between sheets in diff c

    this is outstanding, your the man, man! it is so much more concise to what i had been trying to adapt from the other posts. thanks mate

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: identify unique ID between 2 sheets, auto replace cell values between sheets in diff c

    @ DT123456

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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