Dependent Unique data validation

[Jranchhod]

[TABLE="width: 112"]
<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>[TR]
[TD="width: 70, bgcolor: transparent"]Place[/TD]
[TD="width: 78, bgcolor: transparent"]Person[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maropeng[/TD]
[TD="bgcolor: transparent"]Jetash[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maropeng[/TD]
[TD="bgcolor: transparent"]Jetash[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maropeng[/TD]
[TD="bgcolor: transparent"]Jetash[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Zoo[/TD]
[TD="bgcolor: transparent"]Mark[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maropeng[/TD]
[TD="bgcolor: transparent"]Inkly[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maropeng[/TD]
[TD="bgcolor: transparent"]Sleops[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Zoo Lake[/TD]
[TD="bgcolor: transparent"]Mark[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Zoo Lake[/TD]
[TD="bgcolor: transparent"]Mark[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Zoo Lake[/TD]
[TD="bgcolor: transparent"]Misty Wash[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 452"]
<tbody>[TR]
[TD="width: 604, bgcolor: transparent"] Good day all

[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] I am trying to create a dynamic / dependent data validation. this is what I am trying
[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] under "Places" I create a unique data validation list that woks perfectly in a table form and an Index formular
[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] I am struggling to add the "Staff" data validation dependent to the "Places" validation witch is unique
[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] so if I choose "Maropeng" for example how do I get the "Place" validation to show the unique list for "Place"
[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] so

[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] Maropeng and the list shows , Jetash, Inkly and Sleops and not Jetash, Jetash, Jetash, Inkly and Sleops.
[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] Thank you in advance
[/TD]
[/TR]
[TR]
[TD="width: 604, bgcolor: transparent"] Jetash
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Source[/td][td][/td][td][/td][td]DV list[/td][td][/td][td]Result[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Place[/td][td=bgcolor:#5B9BD5]Person[/td][td][/td][td=bgcolor:#5B9BD5]Place[/td][td][/td][td=bgcolor:#70AD47]Person[/td][td][/td][td]List[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Maropeng[/td][td=bgcolor:#DDEBF7]Jetash[/td][td][/td][td=bgcolor:#DDEBF7]Zoo[/td][td][/td][td=bgcolor:#E2EFDA]Mark[/td][td][/td][td]Maropeng[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Maropeng[/td][td]Jetash[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Zoo[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Maropeng[/td][td=bgcolor:#DDEBF7]Jetash[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Zoo Lake[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Zoo[/td][td]Mark[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Maropeng[/td][td=bgcolor:#DDEBF7]Inkly[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Maropeng[/td][td]Sleops[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Zoo Lake[/td][td=bgcolor:#DDEBF7]Mark[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Zoo Lake[/td][td]Mark[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Zoo Lake[/td][td=bgcolor:#DDEBF7]Misty Wash[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]DV list[/td][td][/td][td]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Place[/td][td][/td][td=bgcolor:#70AD47]Person[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Zoo Lake[/td][td][/td][td=bgcolor:#E2EFDA]Mark[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Misty Wash[/td][/tr]
[/table]

[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