How to compare two columns and remove duplicates?

  • Thread starter Thread starter username123
  • Start date Start date
U

username123

Hi there,

I have two columns in Excel: column A and column B.

Column A has items: car, cat, rat, mat, box
Column B has items: box, truck, cat, car, desk

How do I remove duplicate items from column B? In my case I want th
words "cat" and "car" removed from column B.

Of course, my real columns have thousands of items in them, so I can'
do it by hand.

Excel gurus, help me please!

Thank you
 
Hi username

We need bit more info.
Your example is mentionning
colA ColB
car box
cat truck
rat cat
mat car
box desk

You also mentioned that cat and car should be delete in column
therefor the result should look like this

colA ColB
car box
cat truck
rat desk
mat
box

Is this correct
 
Sorry, I missed one item:

RIGHT NOW I HAVE
colA ColB
car box
cat truck
rat cat
mat car
box desk

WANT TO HAVE

colA ColB
car truck
cat desk
rat
mat
box

Basically, if there are any items in column B that also exist in colum
A, those items should be removed from column B or somehow shown to me a
duplicates, e.g. output in the end of the list or in a separate column
 
Hi username

We need bit more info.
Your example is mentionning
colA ColB
car box
cat truck
rat cat
mat car
box desk

You also mentioned that cat and car should be delete in column
therefor the result should look like this

colA ColB
car box
cat truck
rat desk
mat
box

Is this correct
 
Hi username

Try this

Sub remov_dup()
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("a2" & ":b" & rowcount).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending
Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("a1").Select
For i = 2 To rowcount
Range("a" & i).Select
val1 = Range("a" & i).Value
ActiveCell.Offset(0, 1).Select
Cells.Find(What:=val1, After:=ActiveCell, LookIn:=xlFormulas, LookA
_
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext
MatchCase:= _
False).Activate
val2_add = ActiveCell.Address
If val2_add <> "$A$" & i Then
ActiveCell.ClearContents
End If
Next

End Su
 
Hi username

We need bit more info.
Your example is mentionning
colA ColB
car box
cat truck
rat cat
mat car
box desk

You also mentioned that cat and car should be delete in column B
therefor the result should look like this

colA ColB
car box
cat truck
rat desk
mat
box

Is this correct?
 
thank you for your help.

I run the macro but it doesn't seem to work. Nothing happen
 
in column C use this formula (based on your example, adjust cell ranges to
match reality)

=IF(COUNTIF(A$1:A$5,B1)>0,B1,"")

extend that down to match entries in column B. Duplicates will be marked by
echoing the contents of column B in Column C. That doesn't deal with the
need to delete anything - just shows you which entries appear in both lists.
 
Now, if you use the formula I provided earlier, you can then use this code to
move all remaining visible entries in column C up to begin in row 2 without
any intervening empty cells, and they will be converted to hard values rather
than as the result of the formulas. Makes cutting and moving them elsewhere
easier. If you don't want that done, there's just one line of code in the
section that you have to remove to prevent it:


Sub RemoveEmptyCells()
'assumes you are on the sheet
'with empty cells in column C
'to be removed
'Assumes at least one entry
'in column C somewhere!
Application.ScreenUpdating = False
Range(Range("C65535").End(xlUp).Address).Select
Do While ActiveCell.Row > 1
'carve in stone so you can copy easily
ActiveCell.Formula = ActiveCell.Value
If IsEmpty(ActiveCell) Or ActiveCell.Value = "" Then
'delete cells with no visible content
Selection.Delete Shift:=xlUp ' remains on same row
End If
ActiveCell.Offset(-1, 0).Activate
Loop
Application.ScreenUpdating = False

End Sub
 
Hi username

I have tested my code and everything is ok, can you upload a small
sample of your work sheet?

Denis
 
jetted, he may have gotten sidetracked by the added linebreaks thrown in here.

username123 - in the code that jetted provided, the only time that a line
should extend to another line in your code module is when it ends with a
_

that's a space followed by the underscore character. So if things look like
they broke in the middle of a formula or long word, then it may belong on a
single line in the code module.
 
The next best thing I can think of is to use Replace. Copy a group from one
location on the worksheet to the new location. Note where the constant value
(A1 = 7) was in the old group and where it now appears in the new group.

With the entire group still selected use Edit | Replace to change (A$1) to
the new location, as (A$12) and choose Replace All. Since that's the only
place in any of the cells where I see a $ symbol, then it should give you no
problems at all if everything else in the group of cells copied and pasted is
as you've described here.
 
Disregard the above!! It was a reply intended for another thread. Had the
wrong one open when I typed the response. I apologize for any confusion
caused.
 
Thank you guys for your help!! I have used the if expression and i
worked!

I think my macros are disabled or something
 
Hi username

To activate the macro in excel from the menu bar choose Tool --> Macro
--> Security. From there choose the tab Security Level and pick
medium. Close excel and re-open check your security level if it is
medium then you can use any macro.

Have a nice day
Denis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top