I have a row of data with a lot of blanks in it, similar to this: 10 20 25 "" "" 24 "" 99 "" "" "" 44.
A few rows above it, I have a list of dates, so: 1/1/2020 1/2/2020 (and so on).
What I want to do is grab all the numbers and their accompanying dates, without capturing the blanks at all.
So my Excel sheet would look like:
1/1/2020 1/2/2020 1/3/2020 1/6/2020 1/8/2020 1/12/2020
10 20 25 24 99 44
Instead of:
1/1/2020 1/2/2020 1/3/2020 1/4/2020 1/5/2020 1/6/2020 1/7/2020 1/8/2020
10 20 25 0 0 24 0 99
This formula grabs the first non-blank cell, so it gives me 10:
=INDEX(G2:FM2,MATCH(FALSE,ISBLANK(G2:FM2),0))
And this formula grabs me the accompanying date:
=INDEX(G1:FM1,MATCH(FALSE,ISBLANK(G2:FM2),0))
But I'm struggling with how to pull the formulas forward so it keeps going until it runs out of non-blank cells to pull from.
I tried this instead, but it repeats non-blank cells I've already used instead of giving me the next non-blank. For instance, 24 repeated a few times before going onto 99, instead of just skipping the blank days altogether.
=IF(G2<>"",G2,INDEX(G2:$Q$2,MATCH(FALSE,ISBLANK(G2:$Q$2),0)))
Please advise.
Thank you!
Bookmarks