Formula to fill the blank rows with the value above using a relative row id

Cbastien

Hi everyone!

I'm stuck with this formula I've found on this forum:

=INDEX(ITEM:ITEM; MATCH(MAX(COLLECT(ROWID:ROWID; ROWID:ROWID; @cell <= ROWID@row; ITEM:ITEM; @cell <> "")); ROWID:ROWID; 0))

This formula is used to fill empty cells with last non empty value from above. See my example below:

Since the formula is based on a autonumber column, I can't delete or insert a new row in the middle of the sheet. See line #6.

What I tried is to create an helper column called "row#" with a column formula: =MATCH(ROWID@row; ROWID:ROWID; 0)

to get the relative row position and use it instead of "rowid" but this method lags my sheet way too much.

My question is: Can someone help me to embed these formulas into one to use only the "ROWID" column?

Any help is welcome.

If anything is unclear, just tell me because english is not my first language.

Thank you community!

Paul Newcome

Unfortunately there isn't really a work around for this since you still need to use an INDEX/MATCH to pull the data in. Spreading it all out across multiple columns will actually slow it down further since you are creating even more column formulas referencing entire columns.

MichaelTCA

Hello @Cbastien

Try writing the function as MAX/COLLECT first to pull the right value.

MAX(COLLECT(ROWID:ROWID; ROWID:ROWID; @cell <= ROWID@row; ITEM:ITEM; @cell <> ""))

This will give you your max value. Using it within a MATCH() function will definitely slow down your software and especially when you are indexing a range. The COLLECT() function can blow the latency out of the water if there's a very large list of data.

Then you can use a function like IF(MAX(COLLECT())>ROW#;ROWID@row-ROW#) and it will give you a relative position of whether to ROWID is above or below the ROW# based on the positive or negative result.

Start with getting rid of using MATCH and COLLECT within the same function. Use 1 or the other depending on whether you have 1 criteria (MATCH) or multiple criteria (COLLECT). This alone may help your latency issue.

Cbastien

Hello @MichaelTCA !

First, thank you very much for helping me with this.

I like the first part of what you suggest:

MAX(COLLECT(ROWID:ROWID; ROWID:ROWID; @cell <= ROWID@row; ITEM:ITEM; @cell <> ""))

but I can't figure how to make it relative.. heres the result of the first part (proposed fx column):

here's the link of the sheet if you want to look closer..

https://app.smartsheet.com/b/publish?EQBCT=c6e9efd9c4c741afafc1ac6a12343830

Your help is very appreciated, thank's again😁

Paul Newcome

Unfortunately there isn't really a work around for this since you still need to use an INDEX/MATCH to pull the data in. Spreading it all out across multiple columns will actually slow it down further since you are creating even more column formulas referencing entire columns.

Cbastien

@MichaelTCA

@Paul Newcome

Thanks guys for your quick replies.. much appreciated! You both helped me understand my challenge here. I will try to change my approach and let you know if I find something better...

Have a nice day! 😁

Cbastien

Hi again guys!

I've finnaly found a way that it won't affect my main sheet...

1- I needed to fill the blank rows with the last previous non blank value but this was only to pass the info to another sheet. Let's say I have a product number with a list parts under it. If someone flags a part as defective or missing, the entire line is copied in a non-compliant log but I needed to bring the product number with it.

2- I needed a row number that is relative because if I insert/delete a row, the formula to fill the blank rows stops working properly

Then, I kept the relative ROW # column (=MATCH(ROWID@row; ROWID:ROWID; 0) on the main sheet and moved the "fill blank" formula to the log sheet where the data is required and converted it to cross sheet formula.

Maybe I've just moved the problem but not big issue since that log sheet is used once a week but the main sheet is used all day long by multiple employes.

The main sheet is now 6x times faster!

Thank's for your support!