CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user16731842
user16731842

Reputation: 103

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

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 https://docs.google.com/spreadsheets/d/14iTSYnyKycccVvULCSCyG6cRFNdRoY82YzWSyises2c/copy

image.png

Upvotes: 0

Views: 94

Answers (3)

user14915635
user14915635

Reputation: 396

If you wanted to simply list the last non-empty values for each row that has a value in Column A, you could use the following formula in a separate tab in cell A1, or in the first cell of another column of your choice. I simply added sort to the formula that @ztiaa came up with.

={"Expected Values";sort(index(iferror(substitute(split(trim(transpose(query(substitute(sort(transpose(Sheet1!B2:I),sequence(columns(Sheet1!B2:I)),)," ","❄️"),,9^9)))," "),"❄️"," ")),,1))}

Upvotes: 1

z..
z..

Reputation: 12823

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.

Upvotes: 2

JPV
JPV

Reputation: 27242

In A2 try

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

enter image description here

In case there are spaces in the data, try

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

enter image description here

and see if that helps?

Upvotes: 3

Related Questions