Consolidate countries according to a Code using IF condition in Excel 2003

[CodGuru]

Here's my problem.

I have a list of countries, and I want to ensure that the country code is automatically assigned to each country in a later column.

Here are the countries and codes:

APAC (Asia Pacific)
China
Hong Kong
Japan
Malaysia
Singapore
Taiwan
Thailand
Indonesia

Belux (Belgium Luxembourg)
Belgium
Luxembourg

FR
France
Morocco

GCE
Austria
Germany
Poland

IB
Andorra
Portugal
Spain

INDIA
India

IT
Italy

MEA
Greece
South Africa (SAF)
Swiss
Turkey

NAM
U.S.
Mexico

NL
The Netherlands

SAM
Brasil
Argentina
Chile
Colombia
Peru
Venezuela

UK
United Kingdom

WL
Belgium
France
Germany


You may notice that the last code (WL) is assigned to countries that are already covered by other codes (FR for France, GCE for Germany and BELUX for Belgium).

So to achieve this small automatism I have some concerns. I am unable to put my 32 conditions (32 countries name) on the line. I can do that myself but it creates several columns with duplicates:


=IF(A1="France";"FR";IF(A1="Morocco";"FR";IF(A1="Spain";"IB";IF(A1="Andorra";"IB";IF(A1="Belgium";"B ELUX";IF(A1="Luxembourg";"BELUX";"" ))))))


=F1 & IF(A1="Hong-Kong";"APAC";IF(A1="Japan";"APAC";IF(A1="Malaysia";"APAC";IF(A1="Singapore";"APAC";IF(A1="Taiwan";"A PAC";IF(A1="Thailand";"APAC";"" ))))))

=F1 & IF(A1="Indonesia";"APAC";IF(A1="Austria";"GCE";IF(A1="Germany";"GCE";IF(A1="Poland";"GCE";IF(A1="Por tugal";"IB";IF(A1="India";"INDIA";"" ))))))


Someone would have an easier way to complete my project?

In fact two things that block me:

The first thing is that containing the file when I add the entire list of countries and that I drag formula down to the code, I have to click again on each line or formula is present and that I valid entrance that the formula is taken into account.

I do not understand but I have to do this.

Then my biggest problem is that when I want to retrieve this code in my file, I worry for updating the formula ie I can not make good arguments to the formula of SearchV regarding the logical_test but also to line value_if_true.

So ultimately to be more clear I do not know the appropriate formula in my case.

[Lemog]

Nothing can stop you in the last condition to refer to another cell which would incorporate 7 IF, and then repeat reference to another cell which would incorporate 7 IF and so on.

But it is anything but proper.

SearchV fetches a value in a vertical and return a column value no x in this table.

With your example: SearchV (A1;CountryCode;2;FALSE)

=IF(NB.IF(CountryCode;A1)>0;SearchV(A1;CountryCode;2;FALSE);"" )

[CodGuru]

Yes to the technique you described on the reference to another cell I started like this but this is not unique as you say and then in my case it was created duplicates in different columns and that is why I seek another solution.

For searchV I understood the principle but I do not understand "CountryCode". What is this?

For me it is both Column A (Country) and Column B (Code).

[Lemog]

CountryCode is a name you give to a range of cells.
Can avoid being type $A$1:$B:X or X should be increased.

Once your CountryCode change each time you add a line, there is no need to adjust the formulas. In fact we need not go through Insert, Set. It'll give you a name in a fixed range. It is better than that you select your table, right click, create a list. Then you go to Insert, Set to give it a name.

[switchblade327]

On your Sheet1 from line 7, column 1 you write the code in column 2 you put the country. Be careful that the names would not be preceded by space

Code Country
APAC (Asia Pacific) China
APAC (Asia Pacific) Hong Kong
APAC (Asia Pacific) Japan
APAC (Asia Pacific) Malaysia
APAC (Asia Pacific) Singapore
APAC (Asia Pacific) Taiwan
APAC (Asia Pacific) Thailand
APAC (Asia Pacific) Indonesia
Belux (Belgium Luxembourg) Belgium
Belux (Belgium Luxembourg) Luxembourg
FR France
FR Morocco
FR Austria
GCE Germany
GCE Poland
IB Andorra
IB Portugal
IB Spain
INDIA India
IT Italy


In the Sheet2:

in cells (1,1) you write the note of the country

in cells (1,2) type the formulas =rep(LC(-1)

Function VBA:

Function rep (Country1)
Country1 = Trim (Country1)
Application.Volatile
Dim tab_code 'Create a variable
Set tab_code = CreateObject ("Scripting.Dictionary")

l = 7
col = 2
While Sheets("Sheet1").Cells(l, col) <> ""
country = Trim (Sheets ( "Sheet1"). Cells (l, col))
'Sheets("Sheet1").Cells(l, col) = country
code = Trim (Sheets("Sheet1").Cells (l,col - 1))
If code = "" Then
code = code_old
Else
code_old = code
End If
tab_code (country) = code
l = l + 1
Wend

If tab_code.exists (Country1) Then
rep = tab_code(Country1)
Else
rep = "Undefined"
End If
End Function