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.

 

[xld]

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

which is an array formula, so commit with Ctrl-Shift-Enter.

 

[DonkeyOte]

If you are only looking to return yes/no (1/0) for a match you could use

=COUNTIF(C1:H1,country)

This is an array formula -- press CTRL + ALT + ENTER to force array, will appear as

{=COUNTIF(C1:H1,country)}

then copy down over remaining rows.

EDIT: Just re-read and xld solution does what you require.

 

[dunlopoil]

Brilliant, thanks.

Just want to state how helpful I have found this forum. I've made more progress on real-world problems in a few weeks than in months/years of wading through books which never quite seem to describe the problems you are facing.

Cheers to all!

 

[dunlopoil]

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

which is an array formula, so commit with Ctrl-Shift-Enter.

Hi,
I committed the formula above, but only one country name in the entire database has been matched for some reason. Some of these I am aware of, such as U.K. versus UK, but others I cannot explain. For example, a straightforward country name like Spain with no punctuation or unusual spelling is in one of the columns in the address fields and appears to match exactly the name in the country list but does not transfer to the new column. Can anyone help me to identify what is different about the one country that has been matched (Libya) from all the other entries?

Thanks,
Doug.

 

[xld]

Do any of the values have leading/trailing spaces?

 

[dunlopoil]

Don't know- can you tell me how to identify this?

 

[xld]

You might find this more robust, I assumed U.K. was in the C:H group of data

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

 

[dunlopoil]

You might find this more robust, I assumed U.K. was in the C:H group of data

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

Thanks. I'm not worried about discrepancies like U.K. instead of UK at the moment. I just want to deal with the bulk of the data initially by making as many exact matches as possible. It may be easier if I show an example of how the data may be entered across the different columns (each column entry shown exactly as it is in the DB apart from the space after 'Col *:'

Example 1:
Col C: 11 TANJONG PENJURU CRESCENT,
Col D: SINGAPORE 608974,
Col E:
Col F:
Col G:
Col H:

Example 2:
Col C: AV.REPUBLICA DO CHILE 65,
Col D: 6 ANDAR,SALA 653 - CENTRO,
Col E: 20035 - 900 RIO DE JANEIRO,
Col F: BRAZIL
Col G:
Col H:

Example 3:
Col C: PRINCE CENTRE BUILDING 11,
Col D: JALAN JENDRAL SUDIRMAN NO 3-4
Col E: JAKARTA 10220,
Col F: INDONESIA.
Col G:
Col H:

I tried the amended formula you suggested but it didn't improve the results. I then manually entered the TRIM formula in column E and re-entered your original formula. Again, this made no difference.

Thanks.

 

[xld]

The first formula won't find SINGAPORE, because of the number, or INDONESIA., because of the . The second will find INDONESIA.