How to merge values from one column when another column contains repeated entries in Google Sheets

Other Questions Google Sheets
1

I’m working with a Google Sheets document where I need to combine values from one column whenever there are matching entries in another column.

Here’s what my data looks like:

     Column X    Column Y           Column Z
  1    2456       Chicago           2789/2134
  2    2789       Chicago           2456/2134
  3    2891       Miami             2567
  4    2673       Boston            2901
  5    2901       Boston            2673
  6    2567       Miami             2891
  7    2134       Chicago           2456/2789

What I want to achieve is having Column Z show combined values from Column X, but only when Column Y has matching entries. So if “Chicago” appears multiple times in Column Y, I want to join all the corresponding Column X values with a slash separator.

Is there a formula or method in Google Sheets that can help me accomplish this automatically? I’ve been trying different approaches but haven’t found the right solution yet.

2

Try using QUERY with some helper logic instead. I’ve had good luck with =QUERY(A:C,"SELECT X WHERE Y='"&Y1&"'",0) to grab matching values, then wrap it in TEXTJOIN to concatenate them. You’ll need to handle the dynamic grouping though - that’s the tricky bit. Had the same issue with customer data last month. Ended up doing it in two steps: used UNIQUE to find all distinct values in Column Y, then FILTER with TEXTJOIN to combine the matching X values. =TEXTJOIN("/",TRUE,FILTER(X:X,Y:Y=Y1)) worked really well for me. This approach handles larger datasets way better than array formulas that tend to get sluggish.

3

Just use a pivot table - way easier than messy formulas. Go to Insert > Pivot Table, set column Y as rows and column X as values (choose concatenate). Takes 30 seconds and updates automatically when your data changes. Formulas get weird with dynamic ranges anyway.

4

You can accomplish this task using the combination of ARRAYFORMULA, TEXTJOIN, and IF functions. Input this formula in cell Z1: =ARRAYFORMULA(IF(X1:X<>"",TEXTJOIN("/",TRUE,IF(Y$1:Y$=Y1:Y,X$1:X,"")),"")) and then drag it down to apply it to the rest of the cells. This setup will effectively check for each row in Column Y against all others, concatenating the matching values from Column X separated by slashes. Be sure to adjust the cell ranges to fit your dataset. However, take note that this may slow down if the dataset is extensive, and you could consider alternative methods like using pivot tables or helper columns.