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 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
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...
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
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.
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
Don’t have an account yet? Register yourself now and be a part of our community!