List Duplicates and non Duplicate

[GCLIFTON]

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

 

[BlueHornet]

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10000[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]11555[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]16555[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16555[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11555[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11555[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20000[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=IF( NOT( ISNA( VLOOKUP( A1, A2:$A$7, 1, FALSE))), "Dup", A1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF( NOT( ISNA( VLOOKUP( A2, A3:$A$7, 1, FALSE))), "Dup", A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=IF( NOT( ISNA( VLOOKUP( A3, A4:$A$7, 1, FALSE))), "Dup", A3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=IF( NOT( ISNA( VLOOKUP( A4, A5:$A$7, 1, FALSE))), "Dup", A4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=IF( NOT( ISNA( VLOOKUP( A5, A6:$A$7, 1, FALSE))), "Dup", A5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=IF( NOT( ISNA( VLOOKUP( A6, A7:$A$7, 1, FALSE))), "Dup", A6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[JonXL]

SELECT DISTINCT [Segment 2]
FROM RPMC

 

[BlueHornet]

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