software

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

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,quot;Xquot;,quot;quot;)

Enter this formula in B2:

=IF(OR(A1=A2,A2=A3),quot;Xquot;,quot;quot;)

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

Biff

quot;dbmeyerquot; gt; wrote in message
...
gt;I have a very long column gt;10K of numbers. I need to identify each cell
gt; which has a number before or after it which is equal to it.
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=quot;quot;,quot;quot;,IF(COUNTIF($A$1:A1,A1)gt;1,quot;Xquot;,quot;quot;))
Copy B1 down to B10000

Duplicate numbers, if any, will be flagged with an quot;Xquot;

(We could then insert a new top row, and do a Data gt; Filter gt; Autofilter on
col B to filter out the quot;Xquot;, for example)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;dbmeyerquot; gt; wrote in message
...
gt; I have a very long column gt;10K of numbers. I need to identify each cell
gt; which has a number before or after it which is equal to it.
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 :-)
------------------------------*------------------------------*----------------quot;dbmeyerquot; gt; wrote in message
...
gt;I have a very long column gt;10K of numbers. I need to identify each cell
gt; which has a number before or after it which is equal to it.
Thanks a bunch. I found 699 instances which a customer back charged us 2X
for the same serial number.

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; One way:
gt;
gt; Assume the numbers are in the range A1:A10000 with no empty cells within the
gt; range:
gt;
gt; Enter this formula in B1:
gt;
gt; =IF(A1=A2,quot;Xquot;,quot;quot;)
gt;
gt; Enter this formula in B2:
gt;
gt; =IF(OR(A1=A2,A2=A3),quot;Xquot;,quot;quot;)
gt;
gt; Double click the fill handle to quickly copy the formula down to B10000.
gt;
gt; Biff
gt;
gt; quot;dbmeyerquot; gt; wrote in message
gt; ...
gt; gt;I have a very long column gt;10K of numbers. I need to identify each cell
gt; gt; which has a number before or after it which is equal to it.
gt;
gt;
gt;

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

quot;Maxquot; wrote:

gt; I'm just guessing that you want to flag duplicate numbers in col A
gt; (Numbers are assumed within A1:A10000, and with the possibility of some
gt; blank cells in-between)
gt;
gt; Put in B1:
gt; =IF(A1=quot;quot;,quot;quot;,IF(COUNTIF($A$1:A1,A1)gt;1,quot;Xquot;,quot;quot;))
gt; Copy B1 down to B10000
gt;
gt; Duplicate numbers, if any, will be flagged with an quot;Xquot;
gt;
gt; (We could then insert a new top row, and do a Data gt; Filter gt; Autofilter on
gt; col B to filter out the quot;Xquot;, for example)
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;dbmeyerquot; gt; wrote in message
gt; ...
gt; gt; I have a very long column gt;10K of numbers. I need to identify each cell
gt; gt; which has a number before or after it which is equal to it.
gt;
gt;
gt;

quot;dbmeyerquot; wrote:
gt; WORKS. I made the X a 1 and then sum the
gt; column. 699 Instances of repeat
gt; charges against the same serial number.

Glad to hear that. Good improvisation lt;ggt; !
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---