On Google Sheets, copy the contents of a column to another column but skip some destination cells

Question

On a Google sheets, column A has checkboxes, column C is a list of items.

Copy all the items from column C to column B but skip the destination cells that are on the same row as a checked box from column A.

No cells from column C should be skipped.

Here's the sheet with the desired result in column B:

https://docs.google.com/spreadsheets/d/1DAncxmuTEuk2zuW_h8xC3SNBqOnJ7r-JaJ5gbZkpltU/edit?usp=sharing

I'm struggling to create a formula to do that.

Thank you! :)

I can't come up with a formula to do that. I've been Googling it but no luck. Any hep would be much appreciated. Thank you.

Answer 1

Another approach (tick boxes in A1:A, source list in B1:Bn):

=arrayformula(ifna(vlookup(query(reduce(,A1:index(A:A,counta(B:B)+countif(A:A,true)),lambda(a,r,{a;if(r=true,,max(a)+1)})),"offset 1",0),{row(B:B),B:B},2,0)))

Answer 2

can you try this:

=QUERY(ARRAYFORMULA(SORT({BYROW(A:A,LAMBDA(ladr,IF(ladr=TRUE,{IFERROR(1/0),ROW(ladr)-(COUNTIF(A$1:OFFSET(ladr,-1,0),TRUE))},)));{C:C,row(C:C)}},2,1)),"Select Col1 Where Col2 is not null")

-