Thanks Thanks:  0
Results 1 to 4 of 4

Thread: List Duplicates and non Duplicate

  1. #1
    Board Regular
    Join Date
    Feb 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default List Duplicates and non Duplicate

    Need help.

    I have attempted to search but have unsuccessful. I have link table in Access, In that Table Name RPMC. In Column [Segment2] there are duplicates values and non duplicate values.

    How can i create a query showing the non duplicate values and only 1 value of the duplicate values.

    For example in the [Segment 2]

    10000
    20000
    11555
    16555
    11555
    20000

    Return only
    10000
    20000
    11555
    16555

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Location
    Connecticut
    Posts
    315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List Duplicates and non Duplicate

    There might be a better way to do this, but this is what has worked well for me for a number of years:

    A B C
    1 10000 10000
    2 20000 Dup
    3 11555 Dup
    4 16555 16555
    5 11555 11555
    6 20000 20000
    Sheet2

    Worksheet Formulas
    Cell Formula
    C1 =IF( NOT( ISNA( VLOOKUP( A1, A2:$A$7, 1, FALSE))), "Dup", A1)
    C2 =IF( NOT( ISNA( VLOOKUP( A2, A3:$A$7, 1, FALSE))), "Dup", A2)
    C3 =IF( NOT( ISNA( VLOOKUP( A3, A4:$A$7, 1, FALSE))), "Dup", A3)
    C4 =IF( NOT( ISNA( VLOOKUP( A4, A5:$A$7, 1, FALSE))), "Dup", A4)
    C5 =IF( NOT( ISNA( VLOOKUP( A5, A6:$A$7, 1, FALSE))), "Dup", A5)
    C6 =IF( NOT( ISNA( VLOOKUP( A6, A7:$A$7, 1, FALSE))), "Dup", A6)

    Notice that the cutoff for the lookup range is ONE ROW BEYOND where your data is, so the last lookup is performed into an empty row - where of course no data will be found - so the final value in the column can be checked.
    Last edited by BlueHornet; Apr 19th, 2019 at 09:28 AM.

  3. #3
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    189
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List Duplicates and non Duplicate

    Code:
     
    SELECT DISTINCT [Segment 2]
    FROM RPMC

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Location
    Connecticut
    Posts
    315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List Duplicates and non Duplicate

    Whoops. How did I miss that this was an Access question, and not Excel?

Some videos you may like

User Tag List

Tags for this Thread

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
  •