+ Reply to Thread
Results 1 to 5 of 5

Remove Duplicates That Don't Match Certain Value in Another Column

  1. #1
    Registered User
    Join Date
    04-30-2008
    Posts
    67

    Question Remove Duplicates That Don't Match Certain Value in Another Column

    Good Afternoon All,

    I'm trying to remove the duplicates in Column A of Sheet2 if Column E of the matching value on Sheet1 is not "SUB-ASSY". In other words, I want to remove the duplicates of all the entries on Column A of Sheet2 if it is not listed as "SUB-ASSY" (which is found in Column E of Sheet1). VBA would be preferable.

    Thanks for any help, still trying to wrap my head around vba

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Remove Duplicates That Don't Match Certain Value in Another Column

    This assumes:
    • The values to compare to on Sheet1 are in column A
    • You only want the single cell in Sheet2, column A cleared if conditions are met, not the whole row

    If those assumptions are incorrect, I can fix.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-30-2008
    Posts
    67

    Re: Remove Duplicates That Don't Match Certain Value in Another Column

    This is great

    Couple things (sorry I wasn't clear) - the the values to compare on sheet1 are in column B, and can I get the whole row deleted?

    Would it be like this? I don't think the EntireRow.Delete is right...

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-30-2008
    Posts
    67

    Re: Remove Duplicates That Don't Match Certain Value in Another Column

    Quote Originally Posted by stusic View Post
    I don't think the EntireRow.Delete is right...

    Please Login or Register  to view this content.
    Confirmed, EntireRow.Delete doesn't work. "Compile Error: Invalid or Unqualified Reference"?

  5. #5
    Registered User
    Join Date
    04-30-2008
    Posts
    67

    Re: Remove Duplicates That Don't Match Certain Value in Another Column

    Okay, this is what I've come up with. I adjusted he sheet names, changed the range for "c" to B:B, changed the c.Offset to (0, 3), and used "Cell.EntireRow.Delete".

    Please Login or Register  to view this content.
    However, it appears to not just remove duplicates, but remove all entries for certain items. For example, here is my starting data before and after (on Sheet2, Column A). The items that start with 96J are the ones that are not to be altered, which this does fine, but 57C27219 gets eliminated completely, where it should leave one of the entries...

    The items in bold are the ones that should be there after, the ones in red are the ones tha should be there, but aren't. Not sure why it gets it right sometimes, but not others...

    Before:
    96J56467
    96J56467
    96J56467
    57C27219
    96J56471
    96J56471
    96J56471
    96J56471
    96J56471
    96J56471
    96J56471
    57C27466
    57C27205
    57C27224
    96J56468
    96J56468
    96J56468
    96J56468
    96J56468

    57C27219
    57C27467
    96J56469
    96J56469
    96J56469
    96J56469
    96J56469

    57C27219
    57C27468
    96J56470
    96J56470
    96J56470
    96J56470
    96J56470

    57C27219
    57C27469

    After:
    96J56467
    96J56467
    96J56467
    96J56471
    96J56471
    96J56471
    96J56471
    96J56471
    96J56471
    96J56471
    57C27205
    96J56468
    96J56468
    96J56468
    96J56468
    96J56468
    57C27467
    96J56469
    96J56469
    96J56469
    96J56469
    96J56469
    57C27468
    96J56470
    96J56470
    96J56470
    96J56470
    96J56470
    57C27469

+ 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] index match remove duplicates
    By sick stigma in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-26-2015, 12:22 PM
  2. Index Match from two columns and Remove duplicates,
    By gurkovic in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-16-2014, 05:18 AM
  3. Replies: 5
    Last Post: 05-08-2014, 07:24 AM
  4. remove duplicates based on partial text match
    By smelkin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-16-2011, 09:45 AM
  5. How to remove duplicates from a column
    By wali in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-14-2008, 11:33 AM

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