Find duplicates in column D and delete only if?

Hi, I could really use some advice.

1. Find duplicate item numbers in column D.
2. Then delete the rows with the duplicate item numbers but only if they are listed as "sold out" in column F.

Thank you for your time
Kim

Welcome Tim

In order to get any assistance please read the forum rules i.e. add a excel wb with non confidential or private data then we can help you.


R

Sorry, I'll read them

Yap, this is the second time I've messed up today on this forum, sorry. I need to give this forum a break. I was just tossing out an idea which I now know won't work. I know what I want to do but have no clue how to get there so just testing options and seeing what happens. Thanks

I'm not giving up on the forum and I took it as advice. I have too many ideas on how to get where I want to go that it's not a good time to post because I'd just be tossing out ideas. This is something I will have to do daily to upload to the company I work for websites. I know my end goal but I need to figure out how to get there.

I have a large excel file. In this file column E the Convey # has several matching numbers. They are not duplicates but re-inspection. I need to match those numbers that have PASS in the Inspection Status column H then all the matching number rows are to be deleted. Leaving only the row of with FAIL items that have never passed.

Attachment 716001

It Friday... Enjoy your weekends. This old woman needs a drink and to turn off my brain for a few hours before I work on this all weekend on this.

Judging by the file in the other thread, you could use an advanced filter.
In G2 put
=COUNTIFS(B2:B86,B2,D2:D86,"Pass")>0

Then select A2 & on the data tab select Advanced in the Sort & Filter group, make sure the listrange shows your entire data range & then click in the Criteria Range & select range G1:g2 then click Ok.
You can now delete the visible rows & clear the filter

Hi, I tried to explain this yesterday but did a horrible job and messed up the thread with other ideas. I made examples and hope I can explain what I need better.

My goal is every inspection that has PASS at some point, that row is deleted.

This spreadsheet is a small sample of what I'm working with https://www.dropbox.com/scl/fi/0yhgz...vky4m58pycmsur

Attachment 716122

This second example is where I have found the duplicates which are highlighted. https://www.dropbox.com/scl/fi/u45vv...rbl85z4nqczigq


In this image, you can see the duplicate convey#BC are highlighted with one being FAIL and one being PASS which means both these rows need to be deleted. Note: Some items can be inspected several times before getting a PASS. I can't do it manually because this sheet has 1000's of rows which I will go over a few times a week.

Attachment 716123

Once I have deleted all these duplicates with PASS in at least one row. I will delete all PASS items. Leaving only the truly failed items.

What do I need to learn to do this process?
Thank you for your time
Kim

Sorry, I posted wrong, and not can't find the delete post button. But I will try this next.

See post#11, we posted at the same time.

Originally Posted by Fluff13

Judging by the file in the other thread, you could use an advanced filter.
In G2 put
=COUNTIFS(B2:B86,B2,D2:D86,"Pass")>0

Then select A2 & on the data tab select Advanced in the Sort & Filter group, make sure the listrange shows your entire data range & then click in the Criteria Range & select range G1:g2 then click Ok.
You can now delete the visible rows & clear the filter

Couple Questions:

In =COUNTIFS(B2:B86,B2,D2:D86,"Pass")>0 What does the second B2 for?

Where did the duplicates that have both FAIL and PASS go?

Thank you
Kim

WOW I'll probably be back to ask questions because I really want to learn these codes. Thank you so much, I believe it all works perfectly but need to compare my sheets.

Originally Posted by JLGWhiz

Code needed a mod.

Please Login or Register  to view this content.

Hi, I am trying to learn these codes and have a couple of questions. My questions are marked with >>>>. Attached is a screenshot of my actually headers that I'm working with. I don't understand the (i, 2) parts.


Capture.PNG

Sub t()
Dim i As Long, fn As Range, adr
With ActiveSheet
For i = Cells(Rows.Count, 8).End(xlUp).Row To 6 Step -1

>>> Since in example 1 row 4 is Inspection Status and my full sheet the Inspection Status is E, I change Count 4 to 8.
>>> Row To 3 Step -1 I would change the 3 to a 6


If LCase(Trim(.Cells(i, 4).Value)) = "pass" Then

>>> I change the 4 to an 8 but what does the i stand for? Is it something I need to change?
>>> I've changed the B to E. What does these mean Cells(i, 2)?

If Application.CountIf(Range("E:E"), .Cells(i, 2).Value) > 1 Then
Set fn = .Range("E2:E" & i - 1).Find(.Cells(i, 2).Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
adr = fn.Address
Do
.Cells(fn.Row, 8) = "Pass"
Set fn = .Range("E2:E" & i - 1).FindNext(fn)
Loop While fn.Address <> adr
End If
End If
End If
Next
Intersect(.UsedRange, .Range("H:H")).AutoFilter 1, "PASS"
Intersect(.UsedRange.Offset(1), .Range("H:H")).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

Thank you for your time
Kim

Hi, what do I search online to learn what the i function is? I've been trying to learn (.Cells(i, 4)

Thank you
Kim

Thank you, I've taken several tutorials and will keep this. If you have any thoughts to share I'd really appreciated it. I don't mind doing the work but input is always helpful.

This is where I'm at now

Attachment 716428

I found my mistake. It works!!!!!!

YYYYYAAAAAA