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

 

[etaf]

I could have done -

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

Rather than filter(filter( with 1's and zeros
have a look at choosecols()
where you just need to list the columns you want to show

 

[SanjayGMusafir]

Rather than filter(filter( with 1's and zeros
have a look at choosecols()
where you just need to list the columns you want to show

ChooseCOLS doesn't work in my version of Excel

 

[Fluff]

How about

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

 

[SanjayGMusafir]

How about

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

Thanks @Fluff

Now the challenge is filter them for non blank Emails (26) and Mobile (1)

 

[SanjayGMusafir]

Filter Clients[[Mobile]:[Nm]] where Clients[Mobile] and Clients are not blanks and

Some mistype here

Clients[Mobile] & Clients[Emails] are not blanks

 

[Fluff]

Just wrap it in filter

=filter(INDEX(Clients[[Mobile]:[Nm]],sequence(rows(Clients)),{26,83}),(Clients[Mobile]<>"")*(Clients[Email]<>""))

 

[SanjayGMusafir]

Just wrap it in filter

=filter(INDEX(Clients[[Mobile]:[Nm]],sequence(rows(Clients)),{26,83}),(Clients[Mobile]<>"")*(Clients[Email]<>""))

Strangely @Fluff I also tried that but it didn't work that time. Must have done some silly mistake.

Thanks a lot

 

[SanjayGMusafir]

Just wrap it in filter

=filter(INDEX(Clients[[Mobile]:[Nm]],sequence(rows(Clients)),{26,83}),(Clients[Mobile]<>"")*(Clients[Email]<>""))

@Fluff two more challenges remaining

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

I'm trying various methods but failing over and over again.

Thanks and sorry for the inconvenience

 

[Fluff]

No idea what you mean by

Filter Unique values based on Column Clients (26)