Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have the following table
And I would like a formula that would produce the following result
I would like to iterate through the table going from left to right top to bottom and if there is a value in a cell, I would like to return with the matching top row criteria. How would I do this?
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(C2:O<>"", "♦"&C2:O&"♠"&C1:O1, )),,999^99)),,999^99), "♦")), "♠")))
C2:O isn't empty each value of that range is combined with whats in row 1 eg header and two unique symbols resulting into: ♦1♠XXS for C2. then all is smashed in one single cell with QUERY(TRANSPOSE(QUERY(TRANSPOSE( ),,999^99)),,999^99). once it's all in one cell the string is split by first unique symbol ♦ columns are transposed to rows and again split by 2nd unique symbol ♠. as a final touch, the TRIM is used to fix for possible spaces after each value.