Exposing Duplicates in Columns

  • I thought there was a way to compare two columns and then purge everything that was not a duplicate so that the duplicate fields could be isolated.

    Any ideas? Thanks...

  • I'm sure you can do it with a macro, but first, some clarifying questions.

    By "purge", do you mean delete? Or would you rather hang on to the data, but just put it somewhere else.

    Also, about the duplicates, must they be next to one another (i.e. in cells A1 and B1) or can any cell from column A match with any cell in column B to be considered duplicate?

  • Yes I mean delete the non duplicates. I'm attempting to easily find which records are duplicates

    I was thinking of a way that I could do this all in one column

    For example


    I would want just


    to be flagged or shown

    Or it could be done the opposite way, by deleting the duplicates in the column and then I could compare that to the original column with the duplicates.

    Thanks, hope it's not too confusing...

  • If your column of data is in Col A then put the following formula in col B and copy/paste for the entire data range


    This will leave in column B a list of only those values which are duplicated.

    If a formula won't work then your only other alternative is VBA

    A std XL formula can not clear contents of a range,cell, or worksheet. I'm not even sure if you create your own formula that you can do that kind of operation.

  • Solution by formula

    if original data begins from A1 and sorted then

    B1:displays duplicates
    Ctl+Shift+Enter to get out of the formula bar

    C1:displays non duplicate
    Ctl+Shift+Enter to get out of the formula bar

    then drag down both cells


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!