Collapse range's entries by removing some values
Suppose A1.A9 looks like this:
I am looking for a way, ideally with (plain) formulas in B1.B7 to get:
If I were coding this, it would be easy to do with a do loop. But I'm looking for a solution that I can show people who may be pretty good at basic Excel, but who avoid VBA, array formulas, or programming outside of Excel.
At a more advanced level, ultimately, I'm going to want to do this where my missing values may be coded with not just =na(), but maybe a zero, or even an empty cell.