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

user_19537809 0
Viewed 55

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!

google-sheets filter
1 Answers

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 spreadsheet , include yours to get quicker responses.

Breakdown:

1 - a combination of QUERY function and {} Arrays to order columns without the need for intermediate tables

2 - , to join columns

3 - ; to stack columns

user_195296940
Related