Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: finding matching numbers in a column

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a column of numbers sorted in ascending order. How can I quickly identify those that are identical?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,613
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Of course, you could just use a PivotTable as previously described here...

    http://www.mrexcel.com/board/viewtop...c=5714&forum=2

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    download the file 'compare range' (file nos 12)

    http://www.pexcel.com/download.htm.

    by the way using of count function give me the idea and i have just created a workbook

    uniqueentries

    in this workbook you can add only unique entries in a column.

    well thank to the board for making me think about count functions.....

    ni****h desai
    http://www.pexcel.com

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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