Filter only columns with data and ignore the others

[fleyd]

Hi everyone,

Can someone help with this please?

I have a sheet with many prices (horizontal) and clients (vertical), i know, using excel online and office 365, i can use filter function, but the problem is that i have more than 2 collumns and i would like a way, after choosing the client, only appears the columns with data and ignore the others in blank.

I have an example file so you can see better what i am trying to explain.

Can someone help please? I am not looking for any VBA solutions, just formulas.

 

[Peter Bartholomew]

Assuming you are going to look the row up by index ID rather than manual selection
= FILTER( INDEX(data,ID, ), INDEX(data,ID, )<>"" )
with the corresponding header
= FILTER( header, INDEX(data,ID, )<>"" )

If you have XLOOKUP, the function will return a row of data
= FILTER( XLOOKUP(ID, recordNum, data ), XLOOKUP(ID,recordNum, data )<>"" )
and
= FILTER( header, XLOOKUP(ID,recordNum, data )<>"" )

To combine the data with a filtered header from a single array formula
= FILTER( IF( {0;1}, XLOOKUP(ID,recordNum, data ), header ), XLOOKUP(ID,recordNum, data )<>"" )

 

[fleyd]

Hi,

Sorry but i tried and couldn't get your formulas to work, i never combined filter with index before...
Could i ask you to use my example sheet and fill the formulas please?

My intention is to have a cell where i write (or data validation list) the city and automatically have all the available prices, just the columns with numbers and ignore the columns without any numbers.

I can use the xlookup function, i have it in web version

Sorry to ask, but could you help?

 

[Peter Bartholomew]

OK. I will take a look. I think I deleted the file but I can always reimplement.

Done.
Select a city using the dropdown in the bright yellow cell.

 

[fleyd]

OK. I will take a look. I think I deleted the file but I can always reimplement.

Done.
Select a city using the dropdown in the bright yellow cell.

Thank you so much for the help. I already placed the formula in my work file and is working good (i havent tested the xlookup yet, only the filter and index)

Thanks again!