Get the last non empty column value of every row in the spreadsheet-CodePudding

I want to extract the last non empty value in the column of every row in a spreadsheet I prefer to use arrayformula if thats possible

=transpose(ARRAYFORMULA(COLUMN(B2:ZZZ)))

Here is the spreadheet, that I have tried

CodePudding user response：

In A2 try

=ArrayFormula(iferror(regexextract(trim(transpose(query(transpose(B2:1000),,rows(B2:1000)))), "[^\s] $")))

In case there are spaces in the data, try

=ArrayFormula(iferror(substitute(regexextract(trim(transpose(query(transpose(substitute(B2:1000, " ", "~")),,rows(B2:1000)))) , "[^\s] $"), "~", " ")))

and see if that helps?

CodePudding user response：

Another solution (in A2):

=index(iferror(substitute(split(trim(transpose(query(substitute(sort(transpose(B2:I),sequence(columns(B2:I)),)," ","❄️"),,9^9)))," "),"❄️"," ")),,1)

This reverses the range and uses query smush split index(,,1) to return the first one. The SUBSTITUTE()s are there to account for possible spaces.