# Concatenating two columns using only filled cells in one of them

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.

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.

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 & ",")
))
``````