Closed Thread
Results 1 to 7 of 7

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

  1. #1
    dbmeyer
    Guest

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

    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.

  2. #2
    Biff
    Guest

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

    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" <dbmeyer@discussions.microsoft.com> wrote in message
    news:E602471A-1BC4-4780-9EA9-2C39F3D68069@microsoft.com...
    >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.




  3. #3
    Max
    Guest

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

    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" <dbmeyer@discussions.microsoft.com> wrote in message
    news:E602471A-1BC4-4780-9EA9-2C39F3D68069@microsoft.com...
    > 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.




  4. #4
    Ken Wright
    Guest

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

    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" <dbmeyer@discussions.microsoft.com> wrote in message
    news:E602471A-1BC4-4780-9EA9-2C39F3D68069@microsoft.com...
    >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.




  5. #5
    dbmeyer
    Guest

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

    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" <dbmeyer@discussions.microsoft.com> wrote in message
    > news:E602471A-1BC4-4780-9EA9-2C39F3D68069@microsoft.com...
    > >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.

    >
    >
    >


  6. #6
    dbmeyer
    Guest

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

    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" <dbmeyer@discussions.microsoft.com> wrote in message
    > news:E602471A-1BC4-4780-9EA9-2C39F3D68069@microsoft.com...
    > > 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.

    >
    >
    >


  7. #7
    Max
    Guest

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

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



Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1