Google Sheets Formula Randomly Assign Values To A Column Skipping Row If Adjacent Cell = "Down"

Question

I have 3 columns whose values are randomly generated using this formula

=sort(A1:A36,arrayFormula(randbetween(sign(row(A1:A36)),1000000)),true)

and displayed in columns (H,I,J) and I would like to copy these values into 3 new columns (L,M,N) but skipping the entire row if an adjacent cell within the row = "Down" (Column O).

Below is a screenshot of my current state and problem.

Below is a screenshot of what I want to accomplish using an excel formula. I currently just pasted the values to use as a reference of what I would like to accomplish.

Answer 1

So, you want to copy the data from some columns to some other columns, but skipping the rows where the O column is "Down". Not really specific to randomness of those values. Here is my approach:

=if(O2 <> "Down", offset(H$1, countif(O$1:O1, "<>Down"), 0, 1, 3), )

The offset gets three cells in the row determined by countif, which is based on the number of cells that are not "Down" above the current row. This formula would need to be entered on the second row and copied down. I wasn't able to find a solution that gives the entire array at once with some arrayformula.