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.
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.
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
"dbmeyer" <[email protected]> wrote in message
news:[email protected]...
>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.
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dbmeyer" <[email protected]> wrote in message
news:[email protected]...
> 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.
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" <[email protected]> wrote in message
news:[email protected]...
>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.
Thanks a bunch. I found 699 instances which a customer back charged us 2X
for the same serial number.
"Biff" wrote:
> 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
>
> "dbmeyer" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>
WORKS. I made the X a 1 and then sum the collumn. 699 Instances of repeat
charges against the same serial number.
"Max" wrote:
> 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)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "dbmeyer" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>
"dbmeyer" wrote:
> 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> !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks