Is there an easy way to see if the contents of any of the cells in a
column exist anywhere else in the column?

In words, I need to take the contents of a1 and compare it against all
the other cells in the column a. Then I want to take a2 and do the
same. Then a3 . . .

Thanks

-tom

you might consider sorting the column in order - then count instances of
each value. But if you wish to retain reference to where the duplicate
exists then you will need to run a loop. Do you need to know the first,
last or every instance of a duplicate, or just how many there are ?

--
Cheers
Nigel



"tom" wrote in message
oups.com...
Is there an easy way to see if the contents of any of the cells in a
column exist anywhere else in the column?

In words, I need to take the contents of a1 and compare it against all
the other cells in the column a. Then I want to take a2 and do the
same. Then a3 . . .

Thanks

-tom

Seems the easiest way is to simply sort on the column. What do you want to do
it you find duplicates?
--
David


"tom" wrote:

Is there an easy way to see if the contents of any of the cells in a
column exist anywhere else in the column?

In words, I need to take the contents of a1 and compare it against all
the other cells in the column a. Then I want to take a2 and do the
same. Then a3 . . .

Thanks

-tom

FIND

but code depends on what you want to do if it's not unique...
aircode
used twice Find will do the trick.
find this cell in range from row 1 this col to this cell offset-1,0
put this in a conditional format and turn red since it already showed up in
the col


find this cell in range from this cell offset 1,0 to xldown
put this in a conditional format and turn yellow since this is first
occurance but there are more

"tom" wrote:

Is there an easy way to see if the contents of any of the cells in a
column exist anywhere else in the column?

In words, I need to take the contents of a1 and compare it against all
the other cells in the column a. Then I want to take a2 and do the
same. Then a3 . . .

Thanks

-tom

you might consider sorting the column in order

Every instance, and which cells contain them. Sorting the column is
not an option - the column sort order is important to the layout of the
spreadsheet.

Thanks

-tom

Seems the easiest way is to simply sort on the column. What do you want to do
it you find duplicates?

Just print the cell numbers elsewhere on the worksheet, where there
will then be manual intervention.

-tom

Just wanted to share the final solutions - I played with the find
method of Range, but it just was not happening for me. While ugly, it
solved the problem.

Thanks to all.
------------
Sub checkContents()

Dim intRow As Integer
Dim cellString As String
Dim testString As String
Dim cellAddress As String

For Each cell In Range("k1", "k5072")
intRow = 5072
cellString = cell.Text
cellAddress = cell.Address

Do While intRow < Range(cellAddress).Row
testString = Cells(intRow, 11).Text
If testString = cellString Then
Cells(intRow, 12).Interior.ColorIndex = 46
Cells(intRow, 12).Value = cellAddress
End If
intRow = intRow - 1
Loop

Next cell

MsgBox "Done!"

End Sub
--------------

-tom

Hi Tom,

As long as you are going to intervene manually you could use conditional
formatting. Select the column and enter this in the Formula Is in the
conditional formatting box and of course select a color. If you have
duplicates they will all light up except the last entry. So if you had
duplicated in A3, A35 A55 and A100 all except A100 will be highlighted. So
you would delete, or whatever, the 3, 35 55.

=COUNTIF(A1:A300,A1)1

HTH
Regards,
Howard

"tom" wrote in message
oups.com...
Is there an easy way to see if the contents of any of the cells in a
column exist anywhere else in the column?

In words, I need to take the contents of a1 and compare it against all
the other cells in the column a. Then I want to take a2 and do the
same. Then a3 . . .

Thanks

-tom

=COUNTIF(A1:A300,A1)1

Interesting. I am so Formula ignorant, and you have pointed out ways
in the past that formulas can solves problems without going to VBA.

I will play with this and see how it works.

Thanks for taking the time to respond.

-tom