UNIQUE and FILTER for multiple ROWS

[thewiseguy]

Hi All,

I have a workbook utilizing the UNIQUE and FILTER functions for a cascading dropdown list. My issue is that I would like these functions to be available for multiple rows (approx. 500 rows). Is there a transpose function I can use when building my unique values lists, so that i can continue to use the UNIQUE and FILTER functions for each row?

Any help is appreciated.

 

[Fluff]

You could use something like

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	County	District				Cumbria	Shropshire
2	Cumbria	Eden
3	Shropshire	Shropshire		Devon	Teignbridge	Torridge	Teignbridge
4	Devon	Torridge
5	Greater Manchester	Stockport		Staffordshire	Cannock Chase	Stoke-on-Trent	Cannock Chase
6	County Durham	County Durham		West Yorkshire	Kirklees	Kirklees	Bradford
7	Staffordshire	Stoke-on-Trent
8	Buckinghamshire	Wycombe
9	Lancashire	Pendle
10	North Yorkshire	Hambleton
11	West Midlands	Birmingham
12	West Yorkshire	Kirklees
13	Cornwall	Cornwall
14	West Yorkshire	Kirklees
15	Lancashire	Pendle
16	West Yorkshire	Bradford
17	Cumbria	Eden
18	Derbyshire	Amber Valley
19	Greater Manchester	Oldham
20	Staffordshire	Stoke-on-Trent
21	Staffordshire	Cannock Chase
22	Derbyshire	Derbyshire Dales
23	Devon	Teignbridge
24	Dorset	North Dorset
25	Gloucestershire	Cotswold
26	Greater Manchester	Oldham
27	Greater Manchester	Oldham
28	Lancashire	Blackburn with Darwen
29	Oxfordshire	Cherwell
30	Surrey	Waverley
31	West Midlands	Birmingham
32	West Yorkshire	Bradford
33
Main

Cell Formulas
Range	Formula
F1:V1	F1	=TRANSPOSE(UNIQUE(A2:A32))
F3:G3,F5:G6,F4,F7:F9	F3	=TRANSPOSE(UNIQUE(FILTER($B$2:$B$32,$A$2:$A$32=D3,"")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D3:D9	List	=F$1#
E3:E9	List	=F3#