+ Reply to Thread
Results 1 to 6 of 6

Find Duplicates in column and then based on row criteria delete the row of that Duplicate

  1. #1
    Registered User
    Join Date
    01-20-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Find Duplicates in column and then based on row criteria delete the row of that Duplicate

    Hi - First Timer here
    I have searched for hours and just cannot find exactly what I need here. I have a sheet of data and in Column 'S' there is a list of Reference Codes. I want to find the duplicates in this column but then compare the 'Class' from Column 'G' and only delete the row of the duplicate that is not required. The criteria is :

    If the duplicates 'Class' are 'SNMPTrap' and 'Card' delete the row with 'SNMPTrap'
    If the duplicates 'Class' are 'SNMPTrap' and 'Switch' delete the row with 'SNMPTrap'
    If the duplicates 'Class' are 'Card' and 'Switch' delete the row with 'Card'

    If should also not matter if the 'Class' appears in any order on the list ie.

    If the duplicates 'Class' are 'Card' and 'SNMPTrap' delete the row with 'SNMPTrap'

    The code I have so far is :
    Please Login or Register  to view this content.
    It works fine for just 2 rows of Data. As Soon as there is more rows then it fails to find the duplicates.

    Help Please!!!
    Last edited by bobberuk; 01-20-2016 at 12:10 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Find Duplicates in column and then based on row criteria delete the row of that Duplic

    We all hate to retype your data.

    So please try and post your spreadsheet rather than a picture.


    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Find Duplicates in column and then based on row criteria delete the row of that Duplic

    Your Code is only looking at the row above the iteration. You need to sort and then look for duplicates.

    Also, If you had included a workbook, it would have been easy to adapt the code below to your workbook. So you will have to tweak this to work in your workbook.

    As a note, I am an amateur at best. If there were more than three classifications, my code may have been inefficient. If there is anyone out there who can accomplish this more effectively with consideration to multiple classifications, please, chime in!

    Paste this code into a General Module in the workbook attached at the bottom and run it to see how it functions.

    Sub Duplicates()
    Dim nrange As Range
    Dim nrange2 As Range
    Dim lrow As Long
    lrow = ActiveSheet.UsedRange.Rows.Count
    Set nrange = Sheet1.Range(Cells(1, 1), Cells(lrow, 2))
    Set nrange2 = Sheet1.Range(Cells(1, 1), Cells(lrow, 1))
    nrange.Sort key1:=Range("A1"), Key2:=Range("B1")
    For Each Cell In nrange2
    If Cell.Value = Cell.Offset(1, 0).Value Then
    If Cell.Offset(0, 1).Value = "Card" Then
    Cell.Offset(1, 0).EntireRow.Delete
    If Cell.Value = Cell.Offset(1, 0) Then
    Cell.Offset(1, 0).EntireRow.Delete
    End If
    ElseIf Cell.Offset(0, 1).Value = "SNMPTrap" Then
    Cell.EntireRow.Delete
    End If
    End If
    Next
    End Sub


    Book3.xlsx

  4. #4
    Registered User
    Join Date
    01-20-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Re: Find Duplicates in column and then based on row criteria delete the row of that Duplic

    Thanks for the advice guys, like I said 1st timer. I'll give Raymundus's code a try out, if I'm still struggling I'll try and attach a sample spreadsheet. :-)

  5. #5
    Registered User
    Join Date
    01-20-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Smile Re: Find Duplicates in column and then based on row criteria delete the row of that Duplic

    Thanks Again for your help guys. I used the code Raymundus put together and it worked a treat. However I then found that the sorting affected other parts of the work book on various LookUps. So I have managed to write some code which does what I need and removes duplicates based on criteria in another column without sorting first. probably way around the houses to resolve, I'm sure someone could shorten the code somehow, but it's good enough.

    The Code now looks like this if you're interested:

    Please Login or Register  to view this content.
    Really Appreciate you taking the time to help indirectly
    Last edited by bobberuk; 01-21-2016 at 01:05 PM.

  6. #6
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Find Duplicates in column and then based on row criteria delete the row of that Duplic

    Great! Glad I could help a little. Looks like you use your original method incorporating the loop from my suggestion. If you're good to go, don't forget to mark the thread as solved in the thread tools at the top of the page.

    Thanks!

+ 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] Delete Duplicates based on Criteria
    By yuenk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2014, 11:56 AM
  2. [SOLVED] Delete,sum duplicates - duplicate based on multiple cell match
    By spikerush2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2014, 01:53 PM
  3. [SOLVED] How to find and delete rows based on criteria within a given column
    By Student1990 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2013, 02:35 PM
  4. [SOLVED] Find duplicates in column A, add values from column B (possibly delete duplicates)
    By luarwhite in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2013, 04:34 PM
  5. [SOLVED] Find duplicates, concatenate into different column, sum and delete duplicates
    By rosannang in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2013, 11:23 AM
  6. Delete Duplicates based on 2 criteria 1st Acutal duplicate 2ND PREVIOUS DATE
    By xcruc1at3r in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2011, 09:09 PM
  7. Replies: 2
    Last Post: 06-26-2010, 06:59 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