Return matching row values in other columns from stacked columns with =FILTER()-CodePudding

I stacked columns S through AB in this sheet.

this sheet

with a =filter() function:

=filter({Storage!S2:S;Storage!U2:U;Storage!W2:W;Storage!Y2:Y;Storage!AA2:AA}, len({Storage!S2:S;Storage!U2:U;Storage!W2:W;Storage!Y2:Y;Storage!AA2:AA}))

and it ends up,

like this , as desired

Is there any way to extract the values from other columns like Map, Side, Site, and Win/Loss from the exact rows the filter function returned?

thanks!

CodePudding user response：

I understood that you whant to get "Death" and "Rd Time" of all rounds stacked on each other and join Map, Round, Side, Site, Strat?, Results and Plant? columns to the stacked columns, ok there is a better way than FILTER formula.

in the second sheet where you want the stacked columns on A2 Paste this formula =QUERY({Data!H2:I,Data!A2:G;Data!J2:K,Data!A2:G;Data!L2:M,Data!A2:G;Data!N2:O,Data!A2:G;Data!P2:Q,Data!A2:G}," Select * Where Col1 is not null ")
to get the desired result as shown in the image
you can also make a copy for the