Formula to identify the first blank in a column and start the list over again

Question

Objective is to identify the first blank in a column, and then start the list again in another column, starting from that blank row.

.

My formula in column C is:

=IFNA(XLOOKUP(IF(MATCH(TRUE,ISBLANK(B:B),0)<=A5+1,A5+2-MATCH(TRUE,ISBLANK(B:B),0),""),A:A,B:B),"")

Although this works, it feels overly complicated for what it is doing. Can this be done in a simpler way? This Excel sheet won't be dealing with large quantities of data, so does not need to be overly efficient.

Answer 1

Alternatively:

Formula in C2:

=TOCOL(IF({1,0},"",DROP(TOCOL(B:B,1),1)),,1)

Or, a character shorter:

=TOCOL(REPT(DROP(TOCOL(B:B,1),1),{0,1}),,1)