How do I identify cells with matching numbers within a column.

[dbmeyer]

I have a very long column >10K of numbers. I need to identify each cell
which has a number before or after it which is equal to it.

 

[Biff]

Hi!

One way:

Assume the numbers are in the range A1:A10000 with no empty cells within the
range:

Enter this formula in B1:

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

Enter this formula in B2:

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

Double click the fill handle to quickly copy the formula down to B10000.

Biff

 

[Max]

I'm just guessing that you want to flag duplicate numbers in col A
(Numbers are assumed within A1:A10000, and with the possibility of some
blank cells in-between)

Put in B1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"X",""))
Copy B1 down to B10000

Duplicate numbers, if any, will be flagged with an "X"

(We could then insert a new top row, and do a Data > Filter > Autofilter on
col B to filter out the "X", for example)

 

[Ken Wright]

Just another twist:-

With your data in A1:A10000

in B2 put =AND(A2=A1,B2=A3)

and copy down.

Then just filter on TRUE

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[dbmeyer]

Thanks a bunch. I found 699 instances which a customer back charged us 2X
for the same serial number.

 

[dbmeyer]

WORKS. I made the X a 1 and then sum the collumn. 699 Instances of repeat
charges against the same serial number.

 

[Max]

WORKS. I made the X a 1 and then sum the
column. 699 Instances of repeat
charges against the same serial number.

Glad to hear that. Good improvisation <g> !