Validation lists with no duplicates

[crun2075]

Hello there,

I have the following table

A	B
531​	hello
531​	hi there
540​	goodbye
540​	so long

In C3 is it possible to do two validation lists, one which show only row A and a second to use indirection to only show the corresponding choice in column b based on column A? Hopefully, I am explaining it right and new to indirection and validation lists. I can for the most part create them, just having trouble with the indirection only showing choices made by column B.

Would it also be possible to show the results with no duplicates?

 

[rondeondo]

531	hello	531				531		531	hello
531	hi there	540				540		531	hi there
540	goodbye	531						540	goodbye
540	so long	540						540	so long
531	hello	531	hello						$j$1:$j$2
531	hi there	540	goodbye						$j$3:$j$4
540	goodbye	531							$j$1:$j$2
540	so long	540							$j$3:$j$4

sorry haven't got the column and row headers, but it just goes from a1 across.

J6 code is

=IFERROR("$j$"&MATCH(C6,$I$1:$I$4,0)&":$j$"&MATCH(C6,$I$1:$I$4,0)+COUNTIF($I$1:$I$4,C6)-1,"")

this finds the first instance of the selection in column c and counts how many times that selection appears, so your validation list needs to be in order
validation for column d is

=INDIRECT($J6)

I've used unique for column G but you can just as easily copy in your first column of data and do remove duplicates. Depends if your selection options are going to be static or not

So validation for column c is just

=$G$1:$G$2

 

[crun2075]

Thank you so much so the reply. Could by chance attach an example please? The first formula didn't return what you said it would have (probably a user error on my end). But i also tend be a hands on learner if i can see it already done adapt that way etc, if that makes sense.

 

[rondeondo]

So the active validation is in c6-c9 and d6-d9
let me know how you go

multipleStepAuthentication.xlsx
	A	B	C	D	E	F	G	H	I	J
1	531	hello					531		531	hello
2	531	hi there					540		531	hi there
3	540	goodbye							540	goodbye
4	540	so long							540	so long
5
6			531	hello						$j$1:$j$2
7			540	goodbye						$j$3:$j$4
8			531							$j$1:$j$2
9			540							$j$3:$j$4
Sheet1

Cell Formulas
Range	Formula
G1:G2	G1	=UNIQUE(A1:A4)
J6:J9	J6	=IFERROR("$j$"&MATCH(C6,$I$1:$I$4,0)&":$j$"&MATCH(C6,$I$1:$I$4,0)+COUNTIF($I$1:$I$4,C6)-1,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D6:D9	List	=INDIRECT($J6)
C1:C4,C6:C9	List	=$G$1:$G$2
D1:D4	List	=$J$1:$J$2