Need to get the last not blank text value in a column

Lindsay AR

Hi,

I have an inventory sheet with transactions of check-ins and check-outs. So far it has just been collecting the quantity of checked-in and checked-out to get the overall in stock quantity. Transactions are done via a form that input a new row into bottom of the sheet, with count numbers being updated at the top.

This has been working great. However, I have new items being added that are only check-in and check-out rather than quantity. I need to be able to show the latest status of these items. I pasted an example of the sheet below.

The red values are what should show in the status location above blue line based on the most recent created date.

I have tried formulas with collect, index, max, but I just can't get it. I am able to add a helper column or two, but I can't add a helper column for each item, as their will be too many.

Lindsay AR

For anyone else who may have the same question, I figure it out.

I added a row id column (name 'ugh' because I was very frustrated by this point) and reference the row id rather than the created date. The final formula is as follows:

=INDEX([column name]:[column name], MATCH(MAX(COLLECT([row id]:[row id], [column name]:[column name], ISTEXT(@cell))), [row id]:[row id], 0))

so my formula looked like this using the columns in the screen shot above with the addition of ugh (row id):

=INDEX(b:b, MATCH(MAX(COLLECT(ugh:ugh, b:b, ISTEXT(@cell))), ugh:ugh, 0)) I updated the formula for each column collection I needed

Lindsay AR

Here are some formulas I have tried:

=JOIN(COLLECT([item column]:[item column], Created:Created, MAX(Created:Created))) ---->this results in no value if the column at row is blank

=JOIN(COLLECT([item column]:[item column], Created:Created, MAX(Created:Created), [item column]:[item column], NOT(ISBLANK(@cell ---->this results in no value if the column at row is blank

=INDEX(COLLECT([item column]:[item column], [item column]:[item column], NOT(ISBLANK(@cell))), 1) ---->this only works for items being added to top of the sheet (this is not an option due to other users)

Lindsay AR

For anyone else who may have the same question, I figure it out.

I added a row id column (name 'ugh' because I was very frustrated by this point) and reference the row id rather than the created date. The final formula is as follows:

=INDEX([column name]:[column name], MATCH(MAX(COLLECT([row id]:[row id], [column name]:[column name], ISTEXT(@cell))), [row id]:[row id], 0))

so my formula looked like this using the columns in the screen shot above with the addition of ugh (row id):

=INDEX(b:b, MATCH(MAX(COLLECT(ugh:ugh, b:b, ISTEXT(@cell))), ugh:ugh, 0)) I updated the formula for each column collection I needed