Filtering a list further

[SanjayGMusafir]

Hi Experts
This is the power of Excel - That you start to believe can I go 1 level further.

Recently I had a thread -

Filter Unique Values out of a Multi-Column List

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...

www.mrexcel.com

where @Eric W @ISY @Fluff gave me the solution.

Due to my system constraints only @Eric W formula worked on my system

Which is as below -

=LET(
t,Clients[[PMobile]:[sR2]],
r,ROWS(t),
sel,{1,4,7,10,13,16},
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))

Now If I may be able to Explain my Data Structure that is as below -

PMobile	P	sP	OMobile	O	sO	FMobile	F	sF	MMobile	M	sM	SMobile	S	sS	RMobile	R2	sR2

Here after every column that contains mobile for eg PMobile, I have a column 2 after that has header sP (stands for Status of PMobile) - which indicates me whether the number is working or not. I put "NW" in columns - sP, sO, sF, sM, sS, sR2 if corresponding numbers are not working.

Can we further modify the above formula in a way that the numbers which have "NW" next to them in 2 columns right get eliminated (Filtered) out of the list.

This shall make my life much easy to cross check each number each time. Or say things shall get automated for me.

Any help is much appreciated.
Thanks in advance

Any further clarification required is welcomed. Apologies if I left any ambiguity while explaining.

 

[Fluff]

How about

=LET(t,Clients[[PMobile]:[sR2]],r,ROWS(t),c,COLUMNS(t),s,SEQUENCE(r*c/3,,0),fMob,INDEX(t,MOD(s,r)+1,INT(s/r)*3+{1,2,3}),SORT(UNIQUE(INDEX(FILTER(fMob,INDEX(fMob,,3)<>"nw"),,1)),,-1))

 

[SanjayGMusafir]

How about

=LET(t,Clients[[PMobile]:[sR2]],r,ROWS(t),c,COLUMNS(t),s,SEQUENCE(r*c/3,,0),fMob,INDEX(t,MOD(s,r)+1,INT(s/r)*3+{1,2,3}),SORT(UNIQUE(INDEX(FILTER(fMob,INDEX(fMob,,3)<>"nw"),,1)),,-1))

It did the magic. Thanks @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.