CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query
tehok
tehok

Reputation: 1

How to join cells filtered out from a column by values from another column?

There is a two-column table containing keys in the first column and respective values in the second column. There are repeating keys in the first column, and so the same key can have multiple values. I need values for each key joined into a single cell. So in the end to have a table with no repeating keys in the first column, and joined values for each key in the second column.

I found an easy solution using a filter then transpose then join. But this is a one-line solution (formula has to be copied for each row). Is there a way to achieve this using an array formula? And without using scripts?

Upvotes: 0

Views: 24

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA({SORT(UNIQUE(FILTER(A:A, A:A<>""))), 
 REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(QUERY(QUERY(A1:B, 
 "select count(A) where A <>'' group by A pivot B"), 
 "offset 1", 0)<>"", QUERY(QUERY(A1:B, 
 "select count(A) where A <>'' group by A pivot B"), 
 "limit 0", 1)&",", )),,999^99))), ",$", )})

0

Upvotes: 1

Related Questions