Need to find a formula that pull the last non-blank cell to new column and then repeats with 2nd to last etc. Main issue is that the last cell is not consistently in the same column. See attached image for what needs to happen.
I've already tried several variations of the offset formula, none of which can pull accurately or easily across.
Open to formula or vba solutions.
INDEX/MATCH and some math will do it:
=IFERROR(INDEX($A2:$D2,MATCH("zzz",$A2:$D2)-COLUMN(A:A)+1),"")
The above formula will include any empty cells between the fist and the last, so it B3 where blank it would leave a blank in G3.
If that is not what you want then you can use this instead:
=IFERROR(INDEX($A2:$D2,AGGREGATE(14,6,COLUMN($A2:$D2)/($A2:$D2<>""),COLUMN(A:A))),"")
