# Data Validation to show only unique values

#### bh24524

##### Board Regular
I've tried this without success but I am looking for a data validation in a separate tab to show only unique values among a list of approximately 200 entries starting in Cell M5 of a Tab called "New Bid" and ending at M200 . I've tried Offset formulas with CountA in them according to a video I watched but it is not working. How can I do this?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Fluff

##### MrExcel MVP, Moderator
+Fluff v2.xlsm
ABCDE
1County
3ShropshireShropshire
4DevonDevon
5Greater ManchesterGreater Manchester
6County DurhamCounty Durham
7StaffordshireStaffordshire
8BuckinghamshireBuckinghamshire
9LancashireLancashire
10North YorkshireNorth Yorkshire
11West MidlandsWest Midlands
12West YorkshireWest YorkshireCumbria
13CornwallCornwall
14West YorkshireDerbyshire
15LancashireDorset
16West YorkshireGloucestershire
17CumbriaOxfordshire
18DerbyshireSurrey
19Greater ManchesterHerefordshire
20StaffordshireSouth Yorkshire
21Staffordshire
22Derbyshire
23Devon
24Dorset
25Gloucestershire
26Greater Manchester
27Greater Manchester
28Lancashire
29Oxfordshire
30Surrey
31West Midlands
32West Yorkshire
33Derbyshire
34Greater Manchester
35Herefordshire
36South Yorkshire
37West Yorkshire
38West Yorkshire
39Greater Manchester
40
Lists
Cell Formulas
RangeFormula
E12E12=INDEX(UniqueList,1)
C2:C22C2=IFERROR(INDEX(\$A\$2:\$A\$100,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$100)-ROW(\$A\$2)+1)/(ISNA(MATCH(\$A\$2:\$A\$100,C\$1:C1,0)))/(\$A\$2:\$A\$100<>""),1)),"")
Named Ranges
NameRefers ToCells
UniqueList=OFFSET(Lists!\$C\$2,,,COUNTIF(Lists!\$C:\$C,"?*"))E12, C3:C22
Cells with Data Validation
CellAllowCriteria
E2List=UniqueList

Ignore the formula in E12, it's just to get XL2BB to show the details of the defined name

#### bh24524

##### Board Regular
+Fluff v2.xlsm
ABCDE
1County
3ShropshireShropshire
4DevonDevon
5Greater ManchesterGreater Manchester
6County DurhamCounty Durham
7StaffordshireStaffordshire
8BuckinghamshireBuckinghamshire
9LancashireLancashire
10North YorkshireNorth Yorkshire
11West MidlandsWest Midlands
12West YorkshireWest YorkshireCumbria
13CornwallCornwall
14West YorkshireDerbyshire
15LancashireDorset
16West YorkshireGloucestershire
17CumbriaOxfordshire
18DerbyshireSurrey
19Greater ManchesterHerefordshire
20StaffordshireSouth Yorkshire
21Staffordshire
22Derbyshire
23Devon
24Dorset
25Gloucestershire
26Greater Manchester
27Greater Manchester
28Lancashire
29Oxfordshire
30Surrey
31West Midlands
32West Yorkshire
33Derbyshire
34Greater Manchester
35Herefordshire
36South Yorkshire
37West Yorkshire
38West Yorkshire
39Greater Manchester
40
Lists
Cell Formulas
RangeFormula
E12E12=INDEX(UniqueList,1)
C2:C22C2=IFERROR(INDEX(\$A\$2:\$A\$100,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$100)-ROW(\$A\$2)+1)/(ISNA(MATCH(\$A\$2:\$A\$100,C\$1:C1,0)))/(\$A\$2:\$A\$100<>""),1)),"")
Named Ranges
NameRefers ToCells
UniqueList=OFFSET(Lists!\$C\$2,,,COUNTIF(Lists!\$C:\$C,"?*"))E12, C3:C22
Cells with Data Validation
CellAllowCriteria
E2List=UniqueList

Ignore the formula in E12, it's just to get XL2BB to show the details of the defined name
Thanks for your help in this. This is what I needed. It's unfortunate that my version doesn't support the Sort function otherwise I would have liked to integrate that into the formula somehow. There isn't any other way to do it besides copying and pasting values and then sorting the values, is there?

Replies
3
Views
137
Replies
3
Views
343
Replies
5
Views
60
Replies
0
Views
277
Replies
0
Views
59

1,132,805
Messages
5,655,406
Members
418,197
Latest member
Sumit_Vikram

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

### Which adblocker are you using?

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

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