Exposing Duplicates in Columns

pjordan · Jul 29th 2004

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

jmhans · Jul 29th 2004

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?

pjordan · Jul 30th 2004

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

1
1
2
3
4
4
4
5
6

I would want just

1
1
4
4
4

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

**iwrk4dedpr** · Jul 30th 2004

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

=if(Countif(Range("A:A"),"A1")>1,Range("A1""),"")

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

Code

Sub RemoveUniqueValues()


    For Each rngDAta In Range("A:A")
        If rngDAta = "" Then Exit For
        intNumEntries = Application.WorksheetFunction.CountIf(Range("A:A"), rngDAta)
        If intNumEntries = 1 Then rngDAta.ClearContents
    Next rngDAta


    Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

Display More

Note:
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.

jindon · Jul 30th 2004

Solution by formula

if original data begins from A1 and sorted then

B1:displays duplicates
=INDEX($A$1:$A$200,SMALL(IF(COUNTIF($A$1:$A$200,$A$1:$A$200)>1,ROW($A$1:$A$200)),ROW(A1)),1)
Ctl+Shift+Enter to get out of the formula bar

C1:displays non duplicate
=INDEX($A$1:$A$200,SMALL(IF(COUNTIF($A$1:$A$200,$A$1:$A$200)=1,ROW($A$1:$A$200)),ROW(A1)),1)
Ctl+Shift+Enter to get out of the formula bar

then drag down both cells

jindon

Exposing Duplicates in Columns

Participate now!

Share