Concatenate cell values if they have a similar value in another cell

user_6521743 0
Viewed 56

I need to concatenate the city names that a person has been to into another cell but they have to be repeated every time the name of the person comes up. I tried using COUNTIF but couldn't get me anywhere.

I don't wanna use VB code; just formulas/functions. Any ideas?

enter image description here

Here's the link to my spreadsheet

google-sheets-query gs-vlookup array-formulas google-sheets-formula google-sheets
1 Answers

use:

=ARRAYFORMULA(REGEXREPLACE(IFNA(VLOOKUP(B2:B, 
 TRIM(SPLIT(TRANSPOSE(TRIM(QUERY(QUERY({A2:A&",", B2:B&"♦"},
 "select max(Col1) where Col1 <> ',' group by Col1 pivot Col2")
 ,,99^99))), "♦")), 2, 0)), ",$", ))

enter image description here

user_56326290
Related