Dependent Dropdown - first lookup column contains space between words

[mjcanty]

Hello MrExcel forum,
I have a workbook (Data sheet and Lookup sheet) that I would like to have two dropdown lists on the Data sheet, where the second dropdown is dependent on the selection in the first dropdown. I have all of the lookup values in the Lookup sheet. Based on multiple searches I have made, I'm coming across some issues. The main issue is that I am using Excel 2016, which doesn't have the =UNIQUE function. Also, the column that will drive the first dropdown contains cells where the values have a space in between (i.e. Los Angeles) and in some cases it could be multiple spaces (i.e. San Luis Obispo). Each example I have found online has not worked, thus I am seeking the forum's assistance in my request. Thank you in advance.

County	City

County	City
Contra Costa	Antioch
Contra Costa	Brentwood
Contra Costa	Clayton
Contra Costa	Concord
Contra Costa	Danville
Contra Costa	El Cerrito
Contra Costa	Hercules
Contra Costa	Lafayette
Contra Costa	Martinez
Contra Costa	Moraga
Contra Costa	Oakley
Contra Costa	Orinda
Contra Costa	Pinole
Contra Costa	Pittsburg
Contra Costa	Pleasant Hill
Contra Costa	Richmond
Contra Costa	San Pablo
Contra Costa	San Ramon
Contra Costa	Walnut Creek
Del Norte	Crescent City
El Dorado	Placerville
El Dorado	South Lake Tahoe
Los Angeles	Agoura Hills
Los Angeles	Alhambra
Los Angeles	Arcadia
Los Angeles	Artesia
Los Angeles	Avalon
Los Angeles	Azusa
Los Angeles	Baldwin Park
Los Angeles	Bell
Los Angeles	Bellflower
Los Angeles	Bell Gardens
Los Angeles	Beverly Hills
Los Angeles	Bradbury
Los Angeles	Burbank
Los Angeles	Calabasas
Los Angeles	Carson
Los Angeles	Cerritos
Los Angeles	Claremont
Los Angeles	Commerce
Los Angeles	Compton
Los Angeles	Covina
Los Angeles	Cudahy
Los Angeles	Culver City
Los Angeles	Diamond Bar
Los Angeles	Downey
Los Angeles	Duarte
Los Angeles	El Monte
Los Angeles	El Segundo
Los Angeles	Gardena
Los Angeles	Glendale
Los Angeles	Glendora
Los Angeles	Hawaiian Gardens
Los Angeles	Hawthorne
Los Angeles	Hermosa Beach
Los Angeles	Hidden Hills
Los Angeles	Huntington Park
Los Angeles	Industry
Los Angeles	Inglewood
Los Angeles	Irwindale
Los Angeles	La Canada Flintridge
Los Angeles	La Habra Heights
Los Angeles	Lakewood
Los Angeles	La Mirada
Los Angeles	Lancaster
Los Angeles	La Puente
Los Angeles	La Verne
Los Angeles	Lawndale
Los Angeles	Lomita
Los Angeles	Long Beach
Los Angeles	Los Angeles
Los Angeles	Lynwood
Los Angeles	Malibu
Los Angeles	Manhattan Beach
Los Angeles	Maywood
Los Angeles	Monrovia
Los Angeles	Montebello
Los Angeles	Monterey Park
Los Angeles	Norwalk
Los Angeles	Palmdale
Los Angeles	Palos Verdes Estates
Los Angeles	Paramount
Los Angeles	Pasadena
Los Angeles	Pico Rivera
Los Angeles	Pomona
Los Angeles	Rancho Palos Verdes
Los Angeles	Redondo Beach
Los Angeles	Rolling Hills
Los Angeles	Rolling Hills Estates
Los Angeles	Rosemead
Los Angeles	San Dimas
Los Angeles	San Fernando
Los Angeles	San Gabriel
Los Angeles	San Marino
Los Angeles	Santa Clarita
Los Angeles	Santa Fe Springs
Los Angeles	Santa Monica
Los Angeles	Sierra Madre
Los Angeles	Signal Hill
Los Angeles	South El Monte
Los Angeles	South Gate
Los Angeles	South Pasadena
Los Angeles	Temple City
Los Angeles	Torrance
Los Angeles	Vernon
Los Angeles	Walnut
Los Angeles	West Covina
Los Angeles	West Hollywood
Los Angeles	Westlake Village
Los Angeles	Whittier
San Benito	Hollister
San Benito	San Juan Bautista
San Bernadino	Adelanto
San Bernadino	Apple Valley
San Bernadino	Barstow
San Bernadino	Big Bear Lake
San Bernadino	Chino
San Bernadino	Chino Hills
San Bernadino	Colton
San Bernadino	Fontana
San Bernadino	Grand Terrace
San Bernadino	Hesperia
San Bernadino	Highland
San Bernadino	Loma Linda
San Bernadino	Montclair
San Bernadino	Needles
San Bernadino	Ontario
San Bernadino	Rancho Cucamonga
San Bernadino	Redlands
San Bernadino	Rialto
San Bernadino	San Bernardino
San Bernadino	Twentynine Palms
San Bernadino	Upland
San Bernadino	Victorville
San Bernadino	Yucaipa
San Bernadino	Yucca Valley

 

[Taul]

Hi,
There is good tutorial on Data Validation using two word entries.
How to make Excel Data Validation Dependent Lists

Hope this helps
Paul.