how to join related country and city google sheets

user_5859583 0
Viewed 154

I have a problem with joining two columns based on related city and country names in google sheets or in excel maybe (if that helps)

I have two columns like this columns

Columns O is the name of the cities and column P is the country names

I want to join the cities to the related countries and add a character in between like | separated by comma's

so the result would be like Buenos Aires | Argentina, Lima | Peru, ..... in the corresponding rows

Example: Google Sheets Demo

how am I able to do that?

google-sheets excel
1 Answers

For each column O and P (excluding row 1)

  1. Join everything in the column using a comma, as that's the delimiter that's already present. (Use a filter to exclude blanks.) JOIN(",", FILTER(O2:O, NOT(ISBLANK(O2:O))))
  2. Split the join so that every value is in its own cell. SPLIT(JOIN(",", FILTER(O2:O, NOT(ISBLANK(O2:O)))), ",", FALSE)
  3. Transpose the output for easier manipulation. (Operations are typically easier on columns than rows.) TRANSPOSE(SPLIT(JOIN(",", FILTER(O2:O, NOT(ISBLANK(O2:O)))), ",", FALSE))
  4. Trim each cell to get rid of spaces on either side of the values. ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",", FILTER(O2:O, NOT(ISBLANK(O2:O)))), ",", FALSE))))
  5. Get rid of duplicate values. UNIQUE(ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",", FILTER(O2:O, NOT(ISBLANK(O2:O)))), ",", FALSE)))))

Then you can join the two using the template JOIN(", ", ARRAYFORMULA("city"&" | "&"country")). Ultimately, this formula should give you what you want.

=JOIN(", ",
  ARRAYFORMULA(
    UNIQUE(ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",", FILTER(O2:O, NOT(ISBLANK(O2:O)))), ",", FALSE)))))
    &" | "&
    UNIQUE(ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(JOIN(",", FILTER(P2:P, NOT(ISBLANK(P2:P)))), ",", FALSE)))))
  )
)

enter image description here

user_13294980
Related