remove blank cells from horizontal list

[palaeontology]

I have the following values in the range BF54:DD54 ...

I'm currently using the following formula to return the same list of values into the range F54:BD54 ...

={IF(COLUMN(BF:BF)<=SUM(--($BF53:$DD53<>""))+1,INDEX($BF53:$DD53,0,SMALL(IF($BF53:$DD53<>"",COLUMN($BF53:$DD53)-1,""),COLUMN(F:F)-1)),"")}

however, it's returning a blank in every cell in that range.

Can anyone see where I've gone wrong with my formula ?

Please don't tell me I can use data validation or other methods to do the same ... I definitely want to use a formula for this, for reasons of my own.

Kind regards,

Chris

 

[palaeontology]

I meant to say that I;m trying to write the formula needed to rewrite this list ...

as this list, where the blank column cells have been removed ....

I have about 400 rows that each have their own unique list that need to be re-written with blank cells removed.

Can anyone see why my formula (original post) isn't working ?

Kind regards,

Chris

 

[palaeontology]

I figured it out ...

I needed the following ...

=IF(COLUMN(BF:BF)-56<=SUM(--($BF54:$DD54<>""))+1,INDEX($BF54:$DD54,0,SMALL(IF($BF54:$DD54<>"",COLUMN($BF54:$DD54)-57,""),COLUMN(BF:BF)-57)),"")

 

[Peter_SSs]

Another option
=IFERROR(INDEX($BF54:$DD54,AGGREGATE(15,6,(COLUMN($BF54:$DD54)-COLUMN($BF54)+1)/($BF54:$DD54<>""),COLUMNS($F54:F54))),"")

The advantage of this option is that if any new columns are subsequently inserted at the left of the worksheet, this will still return the same results - yours will not.