Filtering 2 columns that are 57 columns apart

[SanjayGMusafir]

Hi Experts
Just trying to find a way to a new problem

I'm working with a structured table Clients.

• Column Clients[Mobile] - assuming 1
• Column Clients - becomes 26th
• Column Clients[Nm] - becomes 83rd

My requirement -

• Filter Clients[[Mobile]:[Nm]] where Clients[Mobile] and Clients are not blanks and
• Return a Sorted list based on Clients[Nm]

What I was doing using the formula below -

=UNIQUE(FILTER(Clients[Email],(Clients[Mobile]<>"")*(Clients[Email]<>"")))

• and applied XLOOKUP
• The limitation is that results can't be sorted on the basis of XLOOKUP values

I could have done -

=UNIQUE(Filter(FILTER(Clients[[Mobile]:[Nm]],(Clients[Mobile]<>"")*(Clients[Email]<>"")),{83 long series of 0s & 1s}))

But I was thinking of a compact way of doing in 1 go.

So I tried and failed with -

=INDEX(Clients[[Mobile]:[Nm]],,{26,83})

• The challenge is it only returns the first row of the result
• And, if I add filter conditions in row part, it repeats same result in total number of rows

Then, I realized that I must be missing on something very basic and doing some silly effort.

I would have shared XL2BB.

• But with my main file it never works (system hangs).
• For sharing examples here, I have to close my main file and start fresh with a blank workbook.

Hope you will understand.

Need your help and guidance.
Thanks in Advance

 

[SanjayGMusafir]

• Filter Unique values based on Column Clients (26)
• Then sorting on Column Clients[Nm] (83)

@Fluff

Typing Error
Filter Clients Based on Columns Clients (26)
Then Sort Filtered list on Clients[Nm] (83)

 

[Fluff]

If you want to return only certain clients, then just add another criteria to the filter function.

 

[SanjayGMusafir]

If you want to return only certain clients, then just add another criteria to the filter function.

Thanks @Fluff

Used your idea and formula

Added a helper column and a filter to your formula

Thanks again

=SORT(FILTER(INDEX(Clients[[Mobile]:[Nm]],SEQUENCE(ROWS(Clients)),{26,84}),(Clients[Mobile]<>"")*(Clients[Email]<>"")*(Clients[Cn]=1)),2)

 

[Fluff]

Glad we could help & thanks for the feedback.