Google-sheets – Merge variable rows into one cell

formulasgoogle sheets

I have the following data

and I would like to have it organized by individual parts while merging the parts IDs into a cell

is there any way of doing this with Google Sheets?

=JOIN(", "; TRANSPOSE(FILTER($B$2:$B; $A$2:$A = E2)))

Use this formula:

=ADDRESS(ROW(D3),COLUMN(D3))

Output:

$D$3

Try this formula for cell H2:

=JOIN(", ", FILTER(B$1:E$1, B2:E2 = 0))

This filters the header row (B1:E1) on values from B2:E2 which has the value 0. The resulting strings are joined with ,.

You can copy this formula to the other cells in column H by dragging it across. The B2:E2 will adjust automatically to match the other rows.

Feel free to look at and copy the example spreadsheet I've set up.