Cleaning address data

[dunlopoil]

Hi,
I have a database of sales records containing 6 address columns (C-H). I want to extract the country (which may be entered in any of the 6 columns) and place this in a separate column. I have a definitive list of countries on sheet 2 which I have saved as a named range (Country).

I would like to be able to set something up which searches the six address fields on sheet 1 and if a match is found against the Country range, enter this in a separate column (I). A non-code solution would be ideal as I am nowhere near that level yet and want to be able to understand what I am doing as far as possible!

Many thanks,
Doug.

 

[dunlopoil]

Thanks. I realise the examples I posted above are more problematic. However, I'm still struggling to see any difference between the few matches that worked and the majority that failed, where there should be a straightforward match between SPAIN in the DB cell and SPAIN in the Country List. That was why I entered the TRIM formula in column E, as it contains many examples like that. However, no additional matches were found. I presume the problem is with leading/trailing spaces as you suggest. I will work on it some more and see how I get on.

Many thanks for your help,
Doug.

 

[xld]

If it is leading/trailing spaces, the second formula should crack it.

Maybe it is web data? Does this get closer

=IF(ISNA(MATCH(TRUE,ISNUMBER(MATCH(SUBSTITUTE(SUBSTITUTE(TRIM(C2:H2),".",""),CHAR(160),""),CountryList,0)),0)),"",
INDEX(C2:H2,MATCH(TRUE,ISNUMBER(MATCH(SUBSTITUTE(SUBSTITUTE(TRIM(C2:H2),".",""),CHAR(160),""),CountryList,0)),0)))

 

[dunlopoil]

No luck, I'm afraid. The formula is surrounded by braces, which means it is correctly entered as an array formula, am I right?

 

[xld]

Yes, that is correct.

Can you post an example workbook somewhere on one of the file sharing servers, such as cjoint.com?

 

[dunlopoil]

That site is in French- can you recommend any others?

 

[xld]

It is ut it is straight-forward. Click the browse button and go grab your file. Then click Creer le Lien Cjoint, and it will create a new page with a link. Copy the link and paste it here.

 

[dunlopoil]

Yes, that is correct.

Can you post an example workbook somewhere on one of the file sharing servers, such as cjoint.com?

File now posted at http://www.box.net/shared/gjugid9kos

 

[xld]

Blimey, 5.1Mb!

Anyway, the problem was easy, SPAIN in your CountryList is actually SPAIN+space. You could get rid of it, but there are many others, so this formula handles it, and commas and dots

=IF(ISNA(MATCH(TRUE,ISNUMBER(MATCH(SUBSTITUTE(SUBSTITUTE(TRIM(C2:H2),".",""),",",""),TRIM(CountryList),0)),0)),"",
INDEX(C2:H2,MATCH(TRUE,ISNUMBER(MATCH(SUBSTITUTE(SUBSTITUTE(TRIM(C2:H2),".",""),",",""),TRIM(CountryList),0)),0)))

I would add UK and USA to your list.

 

[dunlopoil]

Cheers xld!

 

[dunlopoil]

Just one thing- where do these stray spaces come from? I'm pretty certain that when I created the list I didn't enter a space after the country.

Is it just something you get into the habit of (using the TRIM formula) whenever you create a list like this?