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 the
words "cat" and "car" removed from column B.

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

Excel gurus, help me please!

Thank you!


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564

anybody? I thought this might have been simple.


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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?


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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 column
A, those items should be removed from column B or somehow shown to me as
duplicates, e.g. output in the end of the list or in a separate column.


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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?


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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, LookAt
_
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
val2_add = ActiveCell.Address
If val2_add < "$A$" & i Then
ActiveCell.ClearContents
End If
Next

End Sub


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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?


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

thank you for your help.

I run the macro but it doesn't seem to work. Nothing happens


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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.

"username123" wrote:


anybody? I thought this might have been simple.


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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


"JLatham" wrote:

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.

"username123" wrote:


anybody? I thought this might have been simple.


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564

Hi username

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

Denis


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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.

"jetted" wrote:


Hi username

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

Denis


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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.

"jetted" wrote:


Hi username

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

Denis


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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.

"JLatham" wrote:

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.

"jetted" wrote:


Hi username

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

Denis


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564

Thank you guys for your help!! I have used the if expression and it
worked!

I think my macros are disabled or something.


--
username123
------------------------------------------------------------------------
username123's Profile: http://www.excelforum.com/member.php...o&userid=35863
View this thread: http://www.excelforum.com/showthread...hreadid=556564

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


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=556564