Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a 3 columns, some of which have countries in them like so

I                    J                    K
--------------       -----------------    ----------------
Denmark          
Finland
London               United Kingdom
123 street           London               United Kingdom

The problem is that i want to add the countries into a single cell instead of having them in multiple cells

L
------------
Denmark
Finland
United Kingdom
United Kingdom

I Have tried 

=IF(OR(AND(J1=" United Kingdom","United Kingdom",""),AND(K1=" United Kingdom","United Kingdom",""),AND(L1=" United Kingdom","United Kingdom",""),AND(I1=" United Kingdom","United Kingdom","")),"Correct","Wrong")

and 

=IF(OR(J1=" United Kingdom","United Kingdom",""),OR(K1=" United Kingdom","United Kingdom",""),OR(L1=" United Kingdom","United Kingdom",""),OR(I1=" United Kingdom","United Kingdom","")),"Correct","Wrong")

but this gives me an error

Does anybody know why this is not working or a better way to do this?

share|improve this question

migrated from stackoverflow.com Oct 6 '12 at 23:20

3 Answers

up vote 1 down vote

Try this (in cell L1)

=IF($K1<>"", $K1, IF($J1<>"", $J1, IF($I1<>"", $I1, "Not Provided")))

Have not tested this but should work.

Edit

based on the example you gave i assumed that column K would either be blank or a country. If it can contain spaces then try

=IF(TRIM($K1)<>"", $K1, IF(TRIM($J1)<>"", $J1, IF(TRIM($I1)<>"", $I1, "Not Provided")))

instead.

share|improve this answer
this just seems to print out what ever is in cell 'K', it does not seem to be comparing cell 'K' at all – Niall Oct 5 '12 at 11:16
1  
based on the example you gave i assumed that column K would either be blank or a country. If it can contain spaces then try =IF(TRIM($K1)<>"", $K1, IF(TRIM($J1)<>"", $J1, IF(TRIM($I1)<>"", $I1, "Not Provided"))) instead. – mtariq Oct 5 '12 at 11:25
up vote 0 down vote

I have actually solved the problem using the following formula

=IF(OR(OR(TRIM(J1)="United Kingdom"),OR(TRIM(K1)="United Kingdom"),OR(TRIM(L1)="United Kingdom"),OR(TRIM(I1)="United Kingdom")),"Correct","Wrong")
share|improve this answer
This solution only works for one country, you have many in your example. The solution provided by @mtariq is much more effective. Have you tried using trim() as he suggested in his comments? – Dan Oct 5 '12 at 11:31
also trim(" United Kingdom") returns "United Kingdom", it is more robust than hard coding the leading space. What if a bug causes two leading spaces, or a trailing space? – Dan Oct 5 '12 at 11:33
yes i have tried @mtariq but this way does not work, i know this way only works for 1 country but i only have 7 countries so i done this in 7 different columns and concatenated the columns then after and it worked find – Niall Oct 5 '12 at 12:01
Then rather create a list of your seven countries and make your function check in that list. I have no doubt that you can make @mtariq's method work though unless your issue is that the country is not always the last field (which is not demonstrated in your example) – Dan Oct 5 '12 at 12:07
up vote 0 down vote

An alternative to your solution:

Create a list of your seven countries and create a dynamic named range of this list. Let's say you named it CountryList. You could keep this list on a hidden worksheet if need be. Using a dynamic named range just means you can add a new country to the list without having to adjust any of the formulae

Now in your final column put the formula

use the formula with all the OR() and TRIM() that you posted but instead of TRIM(J1)="United Kingdom" rather use ISERROR(MATCH(J1, CountryList, 0 )) to test if the cell matches something in your range and instead of "Correct" use INDEX(CountryList, MATCH(J1, CountryList)) to extract the name from the list

So putting it all together:

=IF(OR(OR(ISERROR(MATCH(J1, CountryList, 0 ))),OR(ISERROR(MATCH(K1, CountryList, 0 ))),OR(ISERROR(MATCH(L1, CountryList, 0 ))),OR(ISERROR(MATCH(I1, CountryList, 0 )))),INDEX(CountryList, MATCH(J1, CountryList)),"Wrong")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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