Delete rows based on data in other rows

[ChrisCione]

A system we use generates a report which looks like the sample data shown below. Note that names are repeated at least once (and often more than once). The "key" column is the confirmation indicator of Y or N (yes or no). The goal is to eliminate all duplicates under the following conditions: 1) If a person has confirmed (noted by a "Y") that information would only appear once on the report. If a person has NOT confirmed (noted by an "N"), that information would also only appear once. All we need to know is if a person confirmed or not. I've read other threads in the forum, and I'm assuming this will require code, which I have no experience in writing.

Sample data:

First Name	Last Name	Confirmation
Mickey	Mouse	Y
Mickey	Mouse	N
Minnie	Mouse	N
Minnie	Mouse	N
Goofy	Dog	N
Goofy	Dog	N
Goofy	Dog	N
Goofy	Dog	N
Daffy	Duck	Y
Daffy	Duck	Y
Daffy	Duck	N
Daffy	Duck	N

The desired result:

First Name	Last Name	Confirmation
Mickey	Mouse	Y
Minnie	Mouse	N
Goofy	Dog	N
Daffy	Duck	Y

 

[Max1616]

Here's how I would accomplish this with code. Note this code assumes that your data is in columns A-C and that there is no additional data below your table:

Sub Dedupe()

Dim lrow As Long
Dim i As Long
Dim j As Long
Dim Name As String
Dim Con As String

lrow = Cells(Rows.Count, 1).End(xlUp).row

For i = 2 To lrow
    Name = Cells(i, 1) & Cells(i, 2)
    Con = Cells(i, 3)
    
    For j = 2 To lrow
        If j = i Then GoTo nextj
        If Con = "Y" And (Cells(j, 1) & Cells(j, 2) = Name) Then
            Rows(j).Delete
            j = j - 1
            GoTo nextj
        End If
        
        If Con = "N" And (Cells(j, 1) & Cells(j, 2) = Name And Cells(j, 3) = "N" And i < j) Then
            Rows(j).Delete
            j = j - 1
            GoTo nextj
        End If
    
nextj:
    Next j

Next i

End Sub

 

[alansidman]

an alternative with Power Query
Load your table to PQ and run this Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Confirmation] = "Y")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows")
in
    #"Removed Duplicates"

Next, Join the above query back onto the original data

let
    Source = Table.NestedJoin(#"Table1 (2)", {"First Name", "Last Name"}, Table1, {"First Name", "Last Name"}, "Table1", JoinKind.FullOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Confirmation"}, {"Confirmation.1"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Table1", {"First Name", "Last Name", "Confirmation.1"})
in
    #"Removed Duplicates"

 

[ChrisCione]

Here's how I would accomplish this with code. Note this code assumes that your data is in columns A-C and that there is no additional data below your table:

Sub Dedupe()

Dim lrow As Long
Dim i As Long
Dim j As Long
Dim Name As String
Dim Con As String

lrow = Cells(Rows.Count, 1).End(xlUp).row

For i = 2 To lrow
    Name = Cells(i, 1) & Cells(i, 2)
    Con = Cells(i, 3)
   
    For j = 2 To lrow
        If j = i Then GoTo nextj
        If Con = "Y" And (Cells(j, 1) & Cells(j, 2) = Name) Then
            Rows(j).Delete
            j = j - 1
            GoTo nextj
        End If
       
        If Con = "N" And (Cells(j, 1) & Cells(j, 2) = Name And Cells(j, 3) = "N" And i < j) Then
            Rows(j).Delete
            j = j - 1
            GoTo nextj
        End If
   
nextj:
    Next j

Next i

End Sub

Thanks!