Mutible replace problem

[petergg]

Hi Guys,

I hope someone can help. I was asked to created a tool that would would accept a pasted list of countries and then give a count based on separate predefined tiers each country fits into which is fine and was a simple countif function.

But now I have been told the source lists will contain country demonyms. For example instead of ''France'' it will contain ''French account'' or ''United Kingdom'' it will say ''UK Customer'' or some variation but will always contain the country demonym.

Is their a quick way that I can put together a formula that says if it contains ''UK'' anywhere in a cell to replace it with United Kingdom so my existing formulas still will work and also a way to mass implement these without having to create 172 different formulas for each country.

I hope this makes sense, thanks for your help.

Pete

[JohnTopley]

Pete,
I think you will need VBA to do this as there is need to "search" each entry to see if it matches a list of multi-country demonyms.

Can you post a file with a reasonable sample of your data (counrties and their demonyms).

[petergg]

Thanks for the help John.

That's exactly what I need it to do but it has to be able to pick the demonym out of the rest of the text in the cell. Do you think its possible?

Thanks

Pete

[petergg]

Hi John,

I'm not sure how to add a file I hope pasting it here works:

Demonyms

UK Business
New Zealand Personal
French PersonalCustomer
Spanish PersonalCustomer
US PersonalCustomer
Spanish Personal
Croatian Personal
US Personal
Brazilian Personal
Malaysian PersonalCustomer
French Premier
Malaysian Premier
Indian PremierCustomer
French Personal
Dutch Premier
Australian Personal
Swiss PersonalCustomer
French PremierCustomer
Spanish PremierCustomer
Australian PersonalCustomer
Thai PremierCustomer
German Premier
UK PersonalCustomer
Spanish Premier
US Premier
German PremierCustomer
Australian Premier
Italian PremierCustomer

Country Group 1

Armenia
Bahamas
Bahrain
Barbados
Bermuda
Brunei
Bulgaria
Cayman Islands
Cook Islands
Croatia
Dominica
Falkland Islands
Fiji
Svalbard and Jan Mayen Islands
Greenland
Guernsey
Israel
Italy
Japan
Korea (Republic of)
Latvia
Lithuania
Luxembourg
Taiwan
Macau
Marshall Islands
Tokelau
Mauritius
Moldova
Monaco
Montenegro
Montserrat
Netherlands Antilles
Niue
Norfolk Island
Oman
Palau
Poland
Romania
Samoa
United Arab Emirates
United States Minor Outlying Islands
Serbia
Singapore
Slovakia
South Africa
Spain
St. Pierre & Miquelon
St. Vincent and the Grenadines
Uruguay
Vatican City (Holy See)
Virgin Islands (British)
Aland Islands
Andorra
Anguilla
Australia
Austria
Belgium
Canada
Chile
Cocos Islands (Keeling)
Czech Republic
Denmark
Estonia
Finland
France
French Guiana
French Polynesia
French Southern Territories
Germany
Gibraltar
Guadeloupe
Guam
Hong Kong
Hungary
Iceland
Ireland
Isle of Man
Jersey
Liechtenstein
Malta
Netherlands
New Zealand
Northern Mariana Islands (US)
Norway
Pitcairn
Portugal
Puerto Rico
Reunion
Samoa, American (US)
San Marino
Slovenia
St. Helena
Sweden
Switzerland
United Kingdom
United States
Virgin Islands (US)


Country Group 2

Angola
Azerbaijan Republic
Bangladesh
Benin
Burkina Faso
Comoros
Cyprus - Republic of
Dominican Republic
Egypt
Gabon Republic
Gambia
Ghana
Guyana
Aruba
Honduras
Kazakhstan
Kuwait
Kyrgyzstan
Lesotho
Madagascar
Maldives
Mali
Mauritania
Belize
Micronesia
Mongolia
Bhutan
Morocco
Mozambique
Nicaragua
Niger
Panama
Papua New Guinea
Philippines
Russia
Rwanda
Sao Tome & Principe
Sri Lanka
Swaziland
Thailand
Bosnia & Herzegovina
Botswana
Togo
Turkey
Turks and Caicos
Ukraine
Western Sahara
Albania
Antigua & Barbuda
Brazil
Cabo Verde
China
Colombia
Costa Rica
Curacao
El Salvador
Faroe Islands
Georgia
Greece
Grenada
Guatemala
India
Jamaica
Jordan
Kiribati
Macedonia
Malawi
Malaysia
Martinique
Mayotte
Mexico
Namibia
Nauru
New Caledonia
Peru
Qatar
Saudi Arabia
Senegal
Seychelles
Solomon Islands
St. Kitts and Nevis
St. Lucia
Suriname
Timor Leste (East Timor)
Tonga
Trinidad & Tobago
Tunisia
Turkmenistan
Tuvalu
Uzbekistan
Vanuatu
Wallis and Futuna Islands
Zambia


Country Group 3

Afghanistan
Algeria
Argentina
Belarus
Bolivia
Burundi
Cambodia
Cameroon
Central African Republic
Chad
Congo, Democratic Republic of
Congo, Republic of (BRAZZAVILLE)
Cote d' Ivoire
Cuba
Djibouti
Ecuador
Equatorial Guinea
Eritrea
Ethiopia
Guinea Bissau
Guinea, Republic of
Haiti
Indonesia
Iran
Iraq
Kenya
Korea (DPRK)
Kosovo
Laos
Lebanon
Liberia
Libya
Myanmar (Burma)
Nepal
Nigeria
Pakistan
Palestinian Territory
Paraguay
Sierra Leone
Somalia
Sudan
Syria
Tajikistan
Tanzania
Uganda
Venezuela
Viet Nam
Yemen
Zimbabwe

Thanks for your help so far.

Pete

[JohnTopley]

.... Need to link demonyms with a country e.g. United Kingdom==>UK so table has demonymbs in (Say) column A and corresponding country in column B.

There may be more than one demonym per country so separate lone for each.

[JohnTopley]

To upload an Excel file, click "Go Advanced" then "Manage Attachments". Browse, e=select file, "Upload", Close window,

Meanwhile I'll try to do something with the data you posted in the thread

[JohnTopley]

See attached as example. RUN button "Data" invokes macro.

Sheet "Table" contains demonyms vs Country. Sheet "Data" has text containing the demonyms and column B the resulting country.

Sub Get_Country()

Dim SrchRng() As Variant
Dim InVar() As Variant

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim r As Long
Dim i As Long
Dim lastrow As Long

Application.ScreenUpdating = False

Set ws1 = Worksheets("Table")
Set ws2 = Worksheets("Data")


With ws1
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    SrchRng = .Range("A2:B" & lastrow)
End With

ws2.Activate

With ws2

    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    InVar = .Range("A1:A" & lastrow)

For r = 1 To UBound(InVar, 1)
'   Find Table Match ......
    For i = 1 To UBound(SrchRng, 1)
        If InStr(1, UCase(InVar(r, 1)), UCase(SrchRng(i, 1))) > 0 Then
           .Cells(r, 2) = SrchRng(i, 2)
           Exit For
    End If
    Next i
    
Next r

End With

Application.ScreenUpdating = True

End Sub

[petergg]

Thanks John that was really Helpfull