Concatenating two columns using only filled cells in one of them

Question

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,P499C

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.

Answer 1

Short answer

A formula that does the required in the question

=JOIN(",",
      QUERY(
            {ArrayFormula({A1:A6}&{B1:B6}),B1:B6},
            "Select Col1 Where Col2<>''"
      )
 )

Explanation

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.

Answer 2

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

=JOIN(",", ARRAYFORMULA( 
  FILTER($A2:$A7, NOT(ISBLANK(B2:B7))) & FILTER(B2:B7, NOT(ISBLANK(B2:B7)))
))

---

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

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