Filter Unique Values out of a Multi-Column List

[SanjayGMusafir]

Hi Experts,

I wanted to get a one column list out a range. So far have been able to extract a multicolumn list. But I want it to be one column list so that it could be filtered further for unique values.

Got kind of stuck somewhere.

Please help
Thanks in advance

Underneath is the formula I have used to extract data so far

=

LET(fMob,FILTER(Clients[[PMobile]:[sR2]],{1,0,0,1,0,0,1,0,0,1,0,0,1,0,0,1,0,0}),SORT(UNIQUE(fMob),,-1))

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Pmobile	HeadingA	HeadingB	HeadingC	HeadingD	sR2		Z
3		A	B	C	D	E	F		X
4		G	H	I	J	K	L		R
5		M	B	A	X	K	L		P
6		B	C	R	P	O	Z		N
7		B	A	N	A	N	A		M
8									L
9									J
10									I
11									G
12									F
13									D
14									C
15									B
16									A
Sheet10

Cell Formulas
Range	Formula
I2:I16	I2	=LET( t,Clients[#Data], r,ROWS(t), sel,{1,3,4,6}, c,COLUMNS(sel), s,SEQUENCE(r*c,,0), fMob,INDEX(t,MOD(s,r)+1,INDEX(sel,INT(s/r)+1)), SORT(UNIQUE(fMob),,-1) )
Dynamic array formulas.

Adapt the sel variable to include the columns you want.

 

[ISY]

Hi

=SORT(UNIQUE(TOCOL(CHOOSECOLS(Clients[#Data];1;3;4;6);3));;-1)

 

[Fluff]

How about

=SORT(UNIQUE(TOCOL(CHOOSECOLS(Clients[[PMobile]:[sR2]],1,4,7,10,13,16))),,-1)

 

[SanjayGMusafir]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Pmobile	HeadingA	HeadingB	HeadingC	HeadingD	sR2		Z
3		A	B	C	D	E	F		X
4		G	H	I	J	K	L		R
5		M	B	A	X	K	L		P
6		B	C	R	P	O	Z		N
7		B	A	N	A	N	A		M
8									L
9									J
10									I
11									G
12									F
13									D
14									C
15									B
16									A
Sheet10

Cell Formulas
Range	Formula
I2:I16	I2	=LET( t,Clients[#Data], r,ROWS(t), sel,{1,3,4,6}, c,COLUMNS(sel), s,SEQUENCE(r*c,,0), fMob,INDEX(t,MOD(s,r)+1,INDEX(sel,INT(s/r)+1)), SORT(UNIQUE(fMob),,-1) )
Dynamic array formulas.

Adapt the sel variable to include the columns you want.

Worked like charm. Thanks @Eric W

 

[SanjayGMusafir]

Hi

=SORT(UNIQUE(TOCOL(CHOOSECOLS(Clients[#Data];1;3;4;6);3));;-1)

Can't comment on the functionality as it didn't work on my system due to lack of some formula option.

Anyways Thanks for your help @ISY

 

[SanjayGMusafir]

How about

=SORT(UNIQUE(TOCOL(CHOOSECOLS(Clients[[PMobile]:[sR2]],1,4,7,10,13,16))),,-1)

Thanks @Fluff for reverting.

Can't comment on the functionality as it didn't work on my system due to lack of some formula option.

Thanks again for your help

 

[Fluff]

Forgot to check your version.