Filtering HSTACK(ed) List

[SanjayGMusafir]

This is (kind of) in continuation to the post -

Filtering a list further

Hi Experts This is the power of Excel - That you start to believe can I go 1 level further. Recently I had a thread - https://www.mrexcel.com/board/threads/filter-unique-values-out-of-a-multi-column-list.1251328/post-6140661 where @Eric W @ISY @Fluff gave me the solution. Due to my system...

www.mrexcel.com

Where @Fluff gave me a suitable solution as per the times.

Since I have upgraded my system to Excel 2024, I thought of doing things differently and efficiently.

Can't say about doing efficiently as I made my formula longer. And arrived at a new challenge.

Purpose -

To filter a table collect mobile number in one list where

• 1, 4, 7, 10, 13, 16, 19 , 22th columns contain mobile numbers,
• adjacent columns indicating whose number are they, and
• second adjacent column indicating whether to contact them or not (DND)
• And, 105th column contains Name

Now this list needs to be compiled in one array where I get -

• Mobile Numbers
• Whose number is it
• And Name (Wanted to avoid XLookup Hazard)

While I kept working step by step using @Fluff 's solution in post -

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

I made a long complicated formula as below.

Challenge -
With this formula, I get a list where a mobile number (Column 1 of resulting array) is repeated multiple times, there by indicating relationship in column 2 with the person in Column 3.

Help Needed -

• Can there be a better way of writting this formula?
• Can we have some way whereby it filters the mobile numbers and thus appearing only once in the list? And, thus indicating Relationship and Name of the person.

The formula I over-worked is below

Thanks in Advance

=LET(a,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{1,2,3,105}),(Clients[PMobile]<>"")*(Clients[sP]="")),
          b,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{4,5,6,105}),(Clients[OMobile]<>"")*(Clients[sO]="")),
          c,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{7,8,9,105}),(Clients[FMobile]<>"")*(Clients[sF]="")),
          d,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{10,11,12,105}),(Clients[MMobile]<>"")*(Clients[sM]="")),
          e,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{13,14,15,105}),(Clients[SMobile]<>"")*(Clients[sS]="")),
          f,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{16,17,18,105}),(Clients[C1Mobile]<>"")*(Clients[sC1]="")),
          g,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{19,20,21,105}),(Clients[C2Mobile]<>"")*(Clients[sC2]="")),
          h,FILTER(CHOOSECOLS(Clients[[PMobile]:[Nm]],{22,23,24,105}),(Clients[RMobile]<>"")*(Clients[sR2]="")),
i,VSTACK(a,b,c,d,e,f,g,h),
          j,CHOOSECOLS(i,1),
          k,IFS(LEN(j)>10,TEXT(j,"##########"),TRUE,91&j),
          l,CHOOSECOLS(i,2),
          m,CHOOSECOLS(i,3),
          n,CHOOSECOLS(i,4),
o,HSTACK(k,l,n),
          p,UNIQUE(o,TRUE,TRUE),
SORT(p,1,1))