Thanks:  0
Likes:  0

# Thread: finding matching numbers in a column

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

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

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

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

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

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

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

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

ni****h desai
http://www.pexcel.com

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•