2

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

Iman Ghavamabadi
  • 47
  • 6
  • Your sheet isn't shared at the moment - can you change permissions plz? Also, are you looking for Excel and/or Google sheets solutions? – Tom Sharpe May 12 '20 at 07:24
  • Just changed the permissions. I prefer Google Sheets. – Iman Ghavamabadi May 12 '20 at 07:28
  • 2
    Try something like: `=TEXTJOIN(", ",1,UNIQUE(FILTER(A:A,B:B=B2)))` – JvdV May 12 '20 at 07:38
  • 1
    Or something with `QUERY` possibly: `=TEXTJOIN(", ",1,UNIQUE(QUERY(A:B,"SELECT A WHERE B = '"&B2&"'",-1)))`. It's not my forte but there may be a possibility to spill any of the above to the entire column. – JvdV May 12 '20 at 07:52
  • Just perfect. Works like a charm. Thanks – Iman Ghavamabadi May 12 '20 at 08:13
  • 1
    @JvdV - I had to tweak your formula a bit cause if B is empty, it doesn't work properly. Here's what I did: =IF(AC3 <>"",(TEXTJOIN(", ",1,UNIQUE(FILTER(B:B,AC:AC=AC3)))),"") – Iman Ghavamabadi May 13 '20 at 03:37

1 Answers1

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • for some reason, when I drag your formula down to apply it to the whole column, it gives me a #REF! error. – Iman Ghavamabadi May 13 '20 at 03:36
  • its arrayformula eg. you are not supposed to drag it down - it auto calculates for all rows. just delete everything in C column and paste the formula into 2nd row. see your example sheet – player0 May 13 '20 at 07:07