Dependent Unique data validation

[Jranchhod]

Place	Person
Maropeng	Jetash
Maropeng	Jetash
Maropeng	Jetash
Zoo	Mark
Maropeng	Inkly
Maropeng	Sleops
Zoo Lake	Mark
Zoo Lake	Mark
Zoo Lake	Misty Wash

<colgroup><col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody> </tbody>

Good day all

I am trying to create a dynamic / dependent data validation. this is what I am trying

under "Places" I create a unique data validation list that woks perfectly in a table form and an Index formular

I am struggling to add the "Staff" data validation dependent to the "Places" validation witch is unique

so if I choose "Maropeng" for example how do I get the "Place" validation to show the unique list for "Place"

so

Maropeng and the list shows , Jetash, Inkly and Sleops and not Jetash, Jetash, Jetash, Inkly and Sleops.

Thank you in advance

Jetash

<tbody> </tbody>

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER. Then drag formula down.

Excel Workbook
	A	B	C	D	E
1	Place	Person		Place	Person
2	Maropeng	Jetash		Maropeng	Jetash
3	Maropeng	Jetash			Inkly
4	Maropeng	Jetash			Sleops
5	Zoo	Mark
6	Maropeng	Inkly
7	Maropeng	Sleops
8	Zoo Lake	Mark
9	Zoo Lake	Mark
10	Zoo Lake	Misty Wash
Sheet

 

[sandy666]

one way with PowerQuery

Source			DV list		Result
Place	Person		Place		Person		List
Maropeng	Jetash		Zoo		Mark		Maropeng
Maropeng	Jetash						Zoo
Maropeng	Jetash						Zoo Lake
Zoo	Mark
Maropeng	Inkly
Maropeng	Sleops
Zoo Lake	Mark
Zoo Lake	Mark
Zoo Lake	Misty Wash

or

DV list		Result
Place		Person
Zoo Lake		Mark
		Misty Wash

[SIZE=1]// Result
let
    tbl4 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    tbl5 = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Source = Table.NestedJoin(tbl5,{"Place"},tbl4,{"Place"},"tbl4",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "tbl4", {"Person"}, {"Person"}),
    RemDup = Table.Distinct(Expand, {"Person"}),
    ROC = Table.SelectColumns(RemDup,{"Person"})
in
    ROC[/SIZE]

 

[Jranchhod]

Good day everyone


Thank you so much for your help ( Ahoy and Sandy) I have been pulling hair for 3 days now.

Much appreciated
Jetash

 

[sandy666]

You are welcome