+ Reply to Thread
Results 1 to 6 of 6

Delete duplicate rows based on values of cells

  1. #1
    Registered User
    Join Date
    06-17-2014
    Location
    St. Petersburg, FL
    MS-Off Ver
    2010
    Posts
    6

    Delete duplicate rows based on values of cells

    I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process. Any suggestions?

    A---B-
    0--123 <-delete
    0--123 <-delete
    0--123 <-delete
    1--123 <-delete based on this the value of column A
    0--123 <-delete
    0--123 <-delete
    1--321
    1--321
    1--321
    1--321
    1--321

    or

    A---B-
    0--123 <-delete
    0--123 <-delete
    1--123 <-delete
    1--123 <-delete based on this the value of column A
    0--123 <-delete
    0--123 <-delete
    1--321
    1--321
    1--321
    1--321
    1--321
    Last edited by phixate; 06-17-2014 at 01:22 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Delete duplicate rows based on values of cells

    c2=
    Please Login or Register  to view this content.
    d2=
    Please Login or Register  to view this content.
    and drag both down.

    After that you can filter on column D.

    Select the values delete (and after that delete those rows).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-17-2014
    Location
    St. Petersburg, FL
    MS-Off Ver
    2010
    Posts
    6

    Re: Delete duplicate rows based on values of cells

    Hmmm I don't think that's what I'm looking for. Picture Column B as a SKU number for a product and Column A as a binary flag for available or not. There's actually a third column I didn't put in for Size. I want to remove all rows for products available only in 1 or 2 sizes.

    Avail---SKU---Size
    0------123----10 <-delete
    1------123----11 <-delete
    0------123----12 <-delete
    0------123----13 <-delete
    0------123----14 <-delete
    0------123----15 <-delete
    1------321----10
    1------321----11
    0------321----12
    1------321----13
    0------321----14

  4. #4
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Delete duplicate rows based on values of cells

    Could you be a bit more explicit about your criterion (or criteria if more than one) for what's to be deleted and what isn't?
    Your explanation isn't clear to me.

  5. #5
    Registered User
    Join Date
    06-17-2014
    Location
    St. Petersburg, FL
    MS-Off Ver
    2010
    Posts
    6

    Re: Delete duplicate rows based on values of cells

    OK I got this working for me. Thanks so much! This has helped us implement a threshold for size availability in our inventory. I never realized the power of a countifs statement.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Delete duplicate rows based on values of cells

    Thanks for the reply.

    Glad I could help.

+ 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] Compare Rows, Delete duplicate values, Sum values but Retain blank rows
    By chancw in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-24-2013, 07:38 AM
  2. [SOLVED] Delete rows based on duplicate cell, but leaving first and last duplicate.
    By LadyNicole in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2013, 05:07 AM
  3. VBA helps needed to Sum duplicate values and delete duplicate rows
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-03-2013, 11:40 PM
  4. Replies: 0
    Last Post: 06-12-2012, 02:00 PM
  5. Replies: 2
    Last Post: 08-24-2011, 05:42 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