Compare two columns-delete duplicates from just ONE column

Hi,
I have column A that is 1,500 records.
I have column B with 40,000 records.

Among the 40,000 records in column B, duplicates of ALL 1,500 records from column A exist.

My question is: How do I find WHERE the 1,500 dupes are and how can I delete JUST those records? Please help...THANK YOU in advance!

In Column-C use something like this (adjusted for your actual ranges):

=MATCH(B1,$A$1:$A$3,0)>0

Note the use absolute references for the column-A.

Which will return either TRUE or #N/A. Extend the formula down column-C for as many rows as usedin column-B.

Next, use Advanced Filter with criteria and the option to copy the non-matching entries to another location.

Make sure column-C has a header say, "Match" (w/o quotes)

For the AF criteria cell use: ="<>TRUE"

Assuming A1 and B1 are titles and not data, run this macro...it will use an empty column to delete the column B matches in one big delete command.

Option Explicit

Sub DupeRemoval()
'JBeaucaire  (10/7/2009)
Dim LR As Long, LC As Long
LC = Range("A1").SpecialCells(xlCellTypeLastCell).Column + 5
LR = Range("B" & Rows.Count).End(xlUp).Row

Cells(1, LC) = "Key"
Range(Cells(2, LC), Cells(LR, LC)).FormulaR1C1 = "=ISNUMBER(MATCH(RC2,C1,0))"
Cells(1, LC).AutoFilter
Cells(1, LC).AutoFilter Field:=1, Criteria1:="True"
Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).ClearContents
Cells(1, LC).AutoFilter
Columns(LC).ClearContents
Range("B2:B" & LR).Sort [B1], xlAscending
MsgBox LR - Range("B" & Rows.Count).End(xlUp).Row & " items were deleted"
End Sub

=======
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.



EDIT: Tip of the hat to Palmetto, this is the same idea just in a macro form.

marshajr1,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Yes, I agree. Your question helps clarify the original posted solution and understanding that is important.

So I will point out that in the post #3 I have highlighted a portion of the "formula" that is being inserted into the cells. That formula us using the R1C1 notation, so instead of writing $A1, we put RC1 which means (relative row - column 1).

You need to be able to read R1C1 to understand. C2 means Column-2, or column B, C1 means column-1 or column A.

So change those appropriately to use columns E and F, 5 and 6. Let us know how you get on.

If you want to start an separate thread and attach a sample worksheet, I'll take a look.

Glad it helped you. A good recommendation for upgrading to a more recent version of Excel falls here, there is a "Remove Duplicates" option built right in now.