get last non-empty cell value in spreadsheet with importrange

Question

I have a raw spreadsheet with 30k+ rows like below.

I'm trying to create a report on last non-empty cell value for each profile_id. But as this is already data-heavy sheet, we've decided the create the report in a different Report sheet using.

I've tried using =ArrayFormula(LOOKUP(1, ArrayFormula(1/(A2:E2<>"")),A2:E2)) to get the last filled column value, but it requires importing the entire data to reporting sheet, and it means 30K+ formulas in each cell, which is not desired

Ideally what I'm looking for is something similar to

=query(IMPORTRANGE("spreadsheet_url_link","calling_sheet!B:F"),
  "Select COALESCE(Col6, Col5, Col4, Col3, Col2) where Col1 is not null")

Expected Outcome

Any ideas or pointers to achieve this would be helpful here.

how is the raw data sheet being built? is it automated? is it being downloaded? — MattKing, Mar 29 '23 at 15:44
It is partially automated with details like profile information, details etc. and then a team calls on them and record their dispositions like "RNR", "Call back", "Busy" etc. — Bikash Behera, Mar 30 '23 at 04:33
just reverse the automation so that new stuff is at the top? — MattKing, Mar 30 '23 at 15:13

score 1 · Accepted Answer · answered Mar 29 '23 at 12:04

1

You may try this arrayformula:

=let(Σ,importrange("[URL]","Sheet!A2:F"),
       byrow(Σ,lambda(Δ,if(index(Δ,,1)="",,let(Γ,choosecols(Δ,2,3,4,5,6),
                       {choosecols(Δ,1),index(Γ,match(2,1/(Γ<>"")))})))))

answered Mar 29 '23 at 12:04

rockinfreakshow

15,077
3
12
19

Thanks @rockinfreakshow for this neat answer. took a while to understand the working of the components as I was not familiar with these new functions. – Bikash Behera Apr 04 '23 at 06:24

score 1 · Answer 2 · answered Mar 30 '23 at 08:55

This approach is more compact and slightly faster (from a quick test in my hands) than the other answer:

=let(range,importrange("[URL]","Sheet!A2:F"),
{choosecols(range,1),byrow(choosecols(range,2,3,4,5,6),lambda(row,choosecols(row,xmatch("*",row,2,-1))))})

The idea is based on TheMaster's answer here: ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?, the idea being to use XMATCH rather than MATCH to search the rows 'backwards' for the first non-blank cell. You can't avoid having to process all 30k+ rows, but you can at least process fewer cells per row (if as per your example, rows contain more filled cells than empty ones).

using reverse XMATCH is very neat work. THis is very very helpful. — Bikash Behera, Apr 04 '23 at 06:31

score 0 · Answer 3 · answered Mar 29 '23 at 11:36

0

At some point you'll have to process the 30k rows to find the last value. One way to do it would be to do that process in an auxiliary tab in the original sheet with the formula you suggested and then only import those two columns of the auxiliary tab

answered Mar 29 '23 at 11:36

Martín

7,849
2
3
13

get last non-empty cell value in spreadsheet with importrange

3 Answers3