List Duplicates and non Duplicate

GCLIFTON

Board Regular
Joined
Feb 11, 2016
Messages
60
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
There might be a better way to do this, but this is what has worked well for me for a number of years:

ABC
11000010000
220000Dup
311555Dup
41655516555
51155511555
62000020000

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top