finding matching numbers in a column

kimbo

New Member
Joined
Apr 10, 2002
Messages
38
I have a column of numbers sorted in ascending order. How can I quickly identify those that are identical?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top