# Thread: List Duplicates and non Duplicate Thanks: 0 Likes:  1 Post #5264055 (1)

1. ## 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. ## 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.

3. ## Re: List Duplicates and non Duplicate

Code:
```
SELECT DISTINCT [Segment 2]
FROM RPMC```

4. ## Re: List Duplicates and non Duplicate

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

## User Tag List

#### Posting Permissions

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