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:


This 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.

2 Answers 2


Short answer

A formula that does the required in the question

            "Select Col1 Where Col2<>''"


The 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.

Here is a formula that does the above, line-broken and indented for readability:


There is also a simpler formula, but it leaves a trailing comma in the result:

  IF(B2:B7="", "", $A2:$A7 & B2:B7 & ",")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.