如何在Excel中将几乎具有相同名称的国家/地区配对? [英] How to pair countries with almost same names in Excel?

问题描述

我需要帮助如何将一些不同的国家/地区名称配对.问题是我有两个来自不同来源的数据集.在第一张工作表中，国家/地区名称基于ISO名称和快捷方式，但在第二张工作表中，国家/地区名称不是基于ISO名称.

I need help how to pair little bit different names of countries. Problem is that I have two datasets from different sources. In first sheet names of countries are based on ISO names and shortcuts, but in second sheet names are not based on ISO names.

Sheet1:

Sheet2:

我尝试使用VLOOKUP函数将正式的iso名称与Sheet2中的名称配对，但是 United States 的结果是 United Kingdom

I tried use VLOOKUP function to pair official iso name with name from Sheet2, but result of United States was United Kingdom

您有什么想法要避免替换问题名称吗?

Have you got any thoughts how to avoid replacing problem names?

谢谢！

推荐答案

我有一个近似解决方案，它将解决VBA中表的主要部分，如下所示:

I have an approximative solution which would solve a major part of your table in VBA as below:

我的代码正在检查是否可以在第二张工作表中找到该字符串，然后在指标3下方的工作表1中输入相应的结果. 因此，在A2中它尝试使用:
1美利坚合众国
2美利坚合众国
3美利坚合众国
...
13个美国(匹配的国家)
问题是，如果您有一个国家(如英国")与其他列表中的英国(U.K)"不同，则给出的结果将是美利坚合众国.

My Code is checking if it can find the string in the second Sheet and then enter the respective results in Sheet 1 below Metric 3. So in A2 it tries with:
1 United States of America
2 United States of Americ
3 United States of Ameri
...
13 United States (which would be the match)
the issue is that if you have a Country like "United Kingdom" which is different in the other list like "U.K", the result given would be the one of United States of America.

尽管如此，它还是需要一些手工工作.

Nevertheless it takes out some manual work.

Sub Find_Countries()

Dim LastRow As Long
LastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

Dim i As Integer
For i = 2 To LastRow
Dim ra As Range
Dim a As Integer
a = Len(Range("A" & i))

Do
Set ra = Sheets(2).Cells.Find(What:=Left(Sheets(1).Range("A" & i), a), LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

a = a - 1
Loop Until Not ra Is Nothing

Sheets(1).Range("E" & i).Value = ra.Offset(0, 1)
Next i

End Sub

这篇关于如何在Excel中将几乎具有相同名称的国家/地区配对?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！