+ Reply to Thread
Results 1 to 2 of 2

Preventing duplicate entry of a row in Sheet 2 while copying selected rows from Sheet 1

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Question Preventing duplicate entry of a row in Sheet 2 while copying selected rows from Sheet 1

    I have checkboxes placed at the end of each row on Sheet1. The rows which have checboxes selected, are copied to another sheet(Sheet2) on click of a button. However, I want to prevent the same row being inserted again if i check the same row next time. I have a column with unique values which are placed in first column, and which i can use to check the duplicate entry of a row. I'll take the following example, the rows containing in Sheet2 (though i have more number of columns than what is shown here):

    ColA Col B
    A 2
    B 4
    C 5


    Here ColA consists of unique values. Now, next time if i want to select and copy the following Rows from Sheet1, only Q 1 and J 4 should be copied and A 2 shouldn't get copied (since it is already present in Sheet2):

    ColA Col B
    Q 1
    A 2
    J 4

    Please let me know what is the best way to achieve this?

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Preventing duplicate entry of a row in Sheet 2 while copying selected rows from Sheet

    Pavan,

    I would use a dictionary object populated with all the values from the target worksheet. That way you would only have to read all rows on the target worksheet once (when populating the dictionary) as opposed for every row from the source data worksheet. Subsequently you can use the dictionary exists property to determine if the row/value already exists on the target worksheet. Note that using the dictionary object requires a reference to the Microsoft scripting runtime.

    In code:

    Please Login or Register  to view this content.
    If you like my contribution click the star icon!

+ 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