0

I need a formula that can add all the countries with that is in a column.

Example:
In sheet 'Export' I have the below countries of origin:

enter image description here

And in other sheet I need to show all the countries of origin and change the name from RO to Romania or SK to Slovakia, etc...

What I have until now is the formula:

=TEXTJOIN(", ",TRUE, IF(left(Export!Q1,2) = "RO" , "ROMANIA", IF(left(Export!Q1,2) = "US" , "USA", IF(left(Export!Q1,2) = "PT" , "PORTUGALIA", IF(left(Export!Q1,2) = "FR" , "FRANTA", IF(LEFT(Export!Q1,2) = "SK" , "SLOVACIA"))))))

But is not working. It only shows the first country found, not all of them.

Can anyone help?

Link: https://docs.google.com/spreadsheets/d/1-Iz0K-tKKjuur6eF3tvEiEJP6zLjm8tAl0RXZqOwX9M/edit?usp=sharing

3

2 Answers 2

0

EDIT (following OP's request)

And If I want it to show only one time the country? To summarize all of them and to appear only one time.

Like this " Romania , Slovakia , England " in one cell. The purpose is to search in column Q if is that country and if it is, then write it. I need to be written only one time, one country. From on sheet, column Q, what countries are they.

"To show only one time the country" use

=ArrayFormula(UNIQUE(IFERROR( 
           SWITCH(Export!Q1:Q, "RO", "Romania", 
                                "SL", "Slovakia", 
                                "EU", "European Union",
                                "GB", "England")))) 

To show "Like this Romania , Slovakia , England in one cell" try

=ArrayFormula(JOIN(", ",UNIQUE(QUERY(IFERROR( 
           SWITCH(Export!Q1:Q, "RO", "Romania", 
                                "SL", "Slovakia", 
                                "EU", "European Union",
                                "GB", "England")), "where Col1 is not null"))))

So what you want is to change the country abbreviations from RO, SK, EU, GB to Romania, Slovenia, European Union, England etc.

Please use the following formula.

=ArrayFormula(IFERROR( 
           SWITCH(Export!Q1:Q, "RO", "Romania", 
                                "SL", "Slovakia", 
                                "EU", "European Union",
                                "GB", "England")))

enter image description here

(Do adjust the ranges and following the pattern provided add more countries according to your needs)

Functions used:

1
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Feb 18, 2021 at 14:51
0

And If I want it to show only one time the country? To summarize all of them and to appear only one time.

Like this " Romania , Slovakia , England " in one cell. The purpose is to search in column Q if is that country and if it is, then write it. I need to be written only one time, one country. From on sheet, column Q, what countries are they.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.