+ Reply to Thread
Results 1 to 7 of 7

Script to check for duplicates in one column then alter adjacent column values

  1. 04-29-2010, 11:29 AM #1
    SebN
    SebN is offline
    Registered User
    Join Date
    04-29-2010
    Location
    Brighton
    MS-Off Ver
    Excel 2003
    Posts
    3

    Script to check for duplicates in one column then alter adjacent column values

    Hi,

    I hope you can help as I'm sure this shouldn't be too complicated but I'm going round in circles and tearing my hair out!

    I have an excel spreadsheet that has lots of data in it. Columns B and C both hold 11 digit reference numbers. Column C has data in every cell but column B only has data in some of the fields. Column C also has a lot of cells with the same value. Any cells that have the same value in column C will have the corresponding column B value either as an 11 digit number or in some cases i will be blank. If it has a value other than blank, it will be the same for all versions of this duplicate.

    I am trying to write a script that will look through column C and for any duplicate values it will populate the corresponding cell in column B with the correct 11 digit number.

    So for example
    Please Login or Register  to view this content.
    After the script would return

    Please Login or Register  to view this content.
    If anyone can help I would be so so grateful!
    Reply With QuoteRegister To Reply
  2. 04-29-2010, 12:03 PM #2
    StephenR
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Script to check for duplicates in one column then alter adjacent column values

    Select column B.

    Edit > Goto > Special > Blanks > OK.

    Enter in the formula bar =B1 (where B1 is the first entry above a blank)

    Ctrl+Enter.

    Copy/paste special values if you want to remove the formulae.

    Or by code:
    Please Login or Register  to view this content.
    Dave - I think I got this from shg. What comes around...
    Last edited by StephenR; 04-29-2010 at 12:50 PM.
    Reply With QuoteRegister To Reply
  3. 04-29-2010, 12:05 PM #3
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Script to check for duplicates in one column then alter adjacent column values

    Paste this in a standard module:

    Please Login or Register  to view this content.
    This assumes the data to process is the only thing in these columns, and starts in row 2.

    Oh shoot, Steve's is much easier. Good lesson here, look for simplest solution first instead of relying too much on VBA.
    Last edited by davegugg; 04-29-2010 at 12:06 PM. Reason: Saw Steve's response.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave
    Reply With QuoteRegister To Reply
  4. 04-29-2010, 12:08 PM #4
    SebN
    SebN is offline
    Registered User
    Join Date
    04-29-2010
    Location
    Brighton
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Script to check for duplicates in one column then alter adjacent column values

    Hi,

    Thanks for the help but the spreadsheet I'm going to do this on is about 5000 rows long and will have to be done for multiple workbooks so I'm hoping to have a VB script that I can just run as a macro to completely automate the procedure!

    Edit - that was in response to Steve! Checking out your solution now Dave!
    Reply With QuoteRegister To Reply
  5. 04-29-2010, 12:15 PM #5
    elprup
    elprup is offline
    Registered User
    Join Date
    05-01-2006
    Location
    OH
    MS-Off Ver
    Excel 2013
    Posts
    41

    Re: Script to check for duplicates in one column then alter adjacent column values

    One of them could inject some irony into this thread by writing code to carry out Stephen's solution.

    Edit - HA! He did it on his own before I completed my post.
    Last edited by elprup; 04-29-2010 at 12:17 PM.
    Reply With QuoteRegister To Reply
  6. 04-29-2010, 12:20 PM #6
    SebN
    SebN is offline
    Registered User
    Join Date
    04-29-2010
    Location
    Brighton
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Script to check for duplicates in one column then alter adjacent column values

    Hi Dave,

    Thanks very much! Your solution works (almost) perfectly!

    It does fall down however if the last cell of Row B is blank (which it sometimes is), so if I have

    Please Login or Register  to view this content.
    It returns

    Please Login or Register  to view this content.
    So, in this one the last cell in column B should be a 3
    Reply With QuoteRegister To Reply
  7. 04-29-2010, 12:23 PM #7
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Script to check for duplicates in one column then alter adjacent column values

    Well, you could change For j... line of code to have a 3 instead of a 2 (like the For i... line of code), but use StephenR's code, its much more efficient.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1