I have the following table

enter image description here

And I would like a formula that would produce the following result

enter image description here

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?

  • Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask.
    – Rubén
    Jul 13 '19 at 0:47

 IF(C2:O<>"", "♦"&C2:O&"♠"&C1:O1, )),,999^99)),,999^99), "♦")), "♠")))


  • That's quite a formula. Your answer would benefit a lot if you could explain each part of the formula. Jul 14 '19 at 18:38
  • @VidarS.Ramdal if 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.
    – user0
    Jul 14 '19 at 19:06
  • Great - you should edit that into your answer. Comments are temporary. Jul 14 '19 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.