finding matching numbers in a column

[kimbo]

I have a column of numbers sorted in ascending order. How can I quickly identify those that are identical?

[Aladin Akyurek]

On 2002-04-19 11:50, kimbo wrote:
I have a column of numbers sorted in ascending order. How can I quickly identify those that are identical?

You could use conditional formatting that hilites non-unique occurrences.

Use the formula in a Formula Is setup:

=COUNTIF($A$1:$A$100,A1)>1

where $A$1:$A$100 is the range of interest.

[Mark W.]

For a list beginning in cell A2 enter the formula...

=IF(OR(A2=A3,A2=A1),A2,"")

...into B2 and copy down.

[ This Message was edited by: Mark W. on 2002-04-19 11:58 ]

[Mark W.]

Of course, you could just use a PivotTable as previously described here...

http://www.mrexcel.com/board/viewtop...c=5714&forum=2

[Yogi Anand]

Hi Kimbo:
If your numbers start in cell A2, I put the following formula in B2 and then dragged down to highlite all cell values that were replicated ...

=IF(A2=A1,A1,"")


HTH



[ This Message was edited by: Yogi Anand on 2002-04-19 12:15 ]

[Mark W.]

Yogi, here are the differences between our approaches...

{"Data","Yours ","Mine"
;1,"",1
;1,1,1
;1,1,1
;2,"",""
;3,"",3
;3,3,3
;4,"",""
;5,"",5
;5,5,5}

...your's only "flags" the last duplicate row because A2=A1 only "looks backward" whereas OR(A2=A3,A2=A1) "looks" both "backward and forward".

[ This Message was edited by: Mark W. on 2002-04-19 15:28 ]

[nisht]

download the file 'compare range' (file nos 12)

http://www.pexcel.com/download.htm.

by the way using of count function give me the idea and i have just created a workbook

uniqueentries

in this workbook you can add only unique entries in a column.

well thank to the board for making me think about count functions.....

nishith desai
http://www.pexcel.com

[Yogi Anand]

On 2002-04-19 15:25, Mark W. wrote:
Yogi, here are the differences between our approaches...

{"Data","Yours ","Mine"
;1,"",1
;1,1,1
;1,1,1
;2,"",""
;3,"",3
;3,3,3
;4,"",""
;5,"",5
;5,5,5}

...your's only "flags" the last duplicate row because A2=A1 only "looks backward" whereas OR(A2=A3,A2=A1) "looks" both "backward and forward".

[ This Message was edited by: Mark W. on 2002-04-19 15:28 ]

Hi Mark:
I got it! My solution shows replications only, whereas your solution shows all identical groups that have replications, and I believe that's what the original request was about.

Thank you! Thank You!