+ Reply to Thread
Results 1 to 16 of 16

How to compare two columns and remove duplicates?

  1. #1
    Registered User
    Join Date
    06-28-2006
    Posts
    6

    How to compare two columns and remove duplicates?

    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!

  2. #2
    Registered User
    Join Date
    06-28-2006
    Posts
    6
    anybody? I thought this might have been simple.

  3. #3
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    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?

  4. #4
    Registered User
    Join Date
    06-28-2006
    Posts
    6
    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.

  5. #5
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    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?

  6. #6
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    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

  7. #7
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    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?

  8. #8
    Registered User
    Join Date
    06-28-2006
    Posts
    6
    thank you for your help.

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

  9. #9
    JLatham
    Guest

    Re: How to compare two columns and remove duplicates?

    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
    >
    >


  10. #10
    JLatham
    Guest

    Re: How to compare two columns and remove duplicates?

    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
    > >
    > >


  11. #11
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    Hi username

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

    Denis

  12. #12
    JLatham
    Guest

    Re: How to compare two columns and remove duplicates?

    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
    >
    >


  13. #13
    JLatham
    Guest

    Re: How to compare two columns and remove duplicates?

    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
    >
    >


  14. #14
    JLatham
    Guest

    Re: How to compare two columns and remove duplicates?

    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
    > >
    > >


  15. #15
    Registered User
    Join Date
    06-28-2006
    Posts
    6
    Thank you guys for your help!! I have used the if expression and it worked!

    I think my macros are disabled or something.

  16. #16
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1