In my table, column A has row names and other columns have values:  +------+---+| M100 | D || M130 | B || M340 |   || P304 | F || P400 |   || P499 | C |+------+---+I'd like to join nonempty values, preceding them with row names and separating by commas. So, the desired output is: M100A,M130B,P304A,P499CThis is somewhat similar to Concatenating only filled cells except I also have row names. If the query function allowed concatenation of string values, a solution could be something like select concat(A,B) where B!='', subsequently joined. At present this is not supported, however. I post my solution as an answer, but I'd like to see other approaches, as the double-filter formula looks a bit repetitive.
Concatenating two columns using only filled cells in one of them
google spreadsheets;formulas
Short answerA formula that does the required in the question=JOIN(,,      QUERY(            {ArrayFormula({A1:A6}&{B1:B6}),B1:B6},            Select Col1 Where Col2<>''      ) )ExplanationThe above formula has nested three functions, use the matrix handling feature of Google Sheets and the concatenate operator.ArrayFormula({A1:A6}&{B1:B6}) : Concatenates the cell values of each row.{ArrayFormula({A1:A6}&{B1:B6}),B1:B6} : Creates a range with two columns. The second column will be used for filtering.QUERY is used to do the filtering.JOIN is used to create the string separating the elements with a comma.