匹配共同值并将其复制到相应的单元格

发布于 2025-01-30 07:43:03 字数 414 浏览 2 评论 0 原文

数据样本

电子邮件地址的左列（e）相当长10,000+，没有特别的顺序，它是相应的国家（f）。
电子邮件地址（H）的右列是大小的一小部分，但是它包含许多重复值。

我需要将 e 中的所有值与 h 中的所有值进行比较，如果有匹配副本 f 中的值 i 中的相应细胞。

我已经研究了Vlookup，VBA脚本以及公式。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冷默言语 2025-02-06 07:43:03

请尝试下一个代码。它使用数组，并且主要在内存中工作应该非常快：

Sub ExtractMatchingCountry()
 Dim sh As Worksheet, lastRowE As Long, lastRowH As Long, arrEF, arrHI, i As Long, j As Long
 
 Set sh = ActiveSheet
 lastRowE = sh.Range("E" & sh.rows.count).End(xlUp).Row 'last cell in E:E
 lastRowH = sh.Range("H" & sh.rows.count).End(xlUp).Row 'last cell in H:H
 
 arrEF = sh.Range("E2:F" & lastRowE).value 'place the range in an array for faster iteration/processing
 arrHI = sh.Range("H2:I" & lastRowH).value 'place the range in an array for faster iteration/processing
 
 For i = 1 To UBound(arrEF)
    For j = 1 To UBound(arrHI)
        If arrEF(i, 1) = arrHI(j, 1) Then
            arrHI(j, 2) = arrEF(i, 2): Exit For
        End If
    Next j
 Next i
 'drop the processed array content to the range:
 sh.Range("H2").Resize(UBound(arrHI), 2).value = arrHI
End Sub

是否有可能将字符串比较以使其看起来相同，但是有些字母在上层的情况下与较低的情况相比？如果是这样，我也可以调整代码来处理这种情况。现在，它比较了身份字符串。我的意思是“ href =“/cdn-cgi/l/电子邮件保护” class =“ __ cf_email__” data-cfemail =“ baf0d5d2d2494ded5dffac3dffac3dbd25594d94d94d9d9d557d7dd7”>

Please, try the next code. It uses arrays and working mostly in memory should be very fast:

Sub ExtractMatchingCountry()
 Dim sh As Worksheet, lastRowE As Long, lastRowH As Long, arrEF, arrHI, i As Long, j As Long
 
 Set sh = ActiveSheet
 lastRowE = sh.Range("E" & sh.rows.count).End(xlUp).Row 'last cell in E:E
 lastRowH = sh.Range("H" & sh.rows.count).End(xlUp).Row 'last cell in H:H
 
 arrEF = sh.Range("E2:F" & lastRowE).value 'place the range in an array for faster iteration/processing
 arrHI = sh.Range("H2:I" & lastRowH).value 'place the range in an array for faster iteration/processing
 
 For i = 1 To UBound(arrEF)
    For j = 1 To UBound(arrHI)
        If arrEF(i, 1) = arrHI(j, 1) Then
            arrHI(j, 2) = arrEF(i, 2): Exit For
        End If
    Next j
 Next i
 'drop the processed array content to the range:
 sh.Range("H2").Resize(UBound(arrHI), 2).value = arrHI
End Sub

Is there a possibility that the strings to be compared to look the same but some letters to be in Upper case against Lower? If so, I can adapt the code to handle such a situation, too. Now, it compares the identic strings. I mean "[email protected]" is not the same with "[email protected]"...