Data Validation to show only unique values

bh24524

Active Member
Joined
Dec 11, 2008
Messages
252
Office Version
  1. 2013
  2. 2007
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please Upload Example file & Desired results with XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,075
Office Version
  1. 365
Platform
  1. Windows
How about like
+Fluff v2.xlsm
ABCDE
1County
2CumbriaCumbriaDevon
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
 
Solution

bh24524

Active Member
Joined
Dec 11, 2008
Messages
252
Office Version
  1. 2013
  2. 2007
How about like
+Fluff v2.xlsm
ABCDE
1County
2CumbriaCumbriaDevon
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?
 

Forum statistics

Threads
1,136,635
Messages
5,676,902
Members
419,657
Latest member
ExcelAl1

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
Top