vba Excel 查找在删除重复项时水平返回多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14256096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Lookup return multiple values horizontally while removing duplicates
提问by Ampi Severe
I would like to do a vertical lookup for a list of lookup values and then have multiple values returned into columns for each lookup value. I actually managed to do this after a long Google search, this is the code:
我想对查找值列表进行垂直查找，然后将多个值返回到每个查找值的列中。经过长时间的谷歌搜索，我实际上设法做到了这一点，这是代码：
=INDEX(Data!$H:$H000, SMALL(IF($B3=Data!$J:$J000, ROW(Data!$J:$J000)-MIN(ROW(Data!$J:$J000))+1, ""), COLUMN(A)))
Now, my problem is, as you can see in the formula, my lookup range contains 70,000 rows, which means a lot of return values. But most of these return values are double. This means I have to drag above formula over many columns until all lookup values (roughly 200) return #NUM!.
现在，我的问题是，正如您在公式中看到的，我的查找范围包含 70,000 行，这意味着很多返回值。但大多数这些返回值都是双倍的。这意味着我必须将上面的公式拖到许多列上，直到所有查找值（大约 200）返回 #NUM!。
Is there any possible way, I guess VBA is necessary, to return the values after duplicates have been removed? I'm new at VBA and I am not sure how to go about this. Also it takes forever to calculate having so many cells.
有没有可能的方法，我想 VBA 是必要的，在删除重复项后返回值？我是 VBA 的新手，我不知道该怎么做。计算拥有这么多单元格也需要很长时间。
采纳答案by barry houdini
[Edited]
[编辑]
You can do what you want with a revised formula, not sure how efficient it will be with 70,000 rows, though.
您可以使用修改后的公式做您想做的事情，但不确定 70,000 行的效率如何。
Use this formula for the first match
使用此公式进行第一场比赛
=IFERROR(INDEX(Data!$H3:$H70000,MATCH($B3,Data!$J3:$J70000,0)),"")
=IFERROR(INDEX(Data!$H3:$H70000,MATCH($B3,Data!$J3:$J70000,0)),"")
Now assuming that formula in in F5use this formula in G5 confirmed with CTRL+SHIFT+ENTERand copied across
现在假设F5 中的公式在 G5 中使用此公式确认CTRL+SHIFT+ENTER并复制
=IFERROR(INDEX(Data!$H3:$H70000,MATCH(1,($B3=Data!$J3:$J70000)*ISNA(MATCH(Data!$H3:$H70000,$F5:F5,0)),0)),"")
=IFERROR（索引（数据！$H3：$H70000，匹配（1，（$B3=数据！$J3：$J70000）*ISNA（匹配（数据！$H3：$H70000，$F5：F5，0 ）） ,0)),"")
changed the bolded part depending on location of formula 1
根据公式 1 的位置更改了粗体部分
This will give you a list without repeats.....and when you run out of values you get blanks rather than an error
这会给你一个没有重复的列表......当你用完值时，你会得到空白而不是错误
回答by MattCrum
Not sure if you're still after a VBA answer but this should do the job - takes about 25 seconds to run on my machine - it could probably be accelerated by the guys on this forum:
不确定您是否仍在使用 VBA 答案，但这应该可以完成工作-在我的机器上运行大约需要 25 秒-本论坛上的人可能会加速它：
Sub ReturnValues()
Dim rnSearch As Range, rnLookup As Range, rnTemp As Range Dim varArray
As Variant Dim lnIndex As Long Dim strTemp As String
Set rnSearch = Sheet1.Range("A1:A200") 'Set this to your 200 row value range
Set rnLookup = Sheet2.Range("A1:B70000") 'Set this to your lookup range (assume 2
columns)
varArray = rnLookup
For Each rnTemp In rnSearch
For lnIndex = LBound(varArray, 1) To UBound(varArray, 1)
strTemp = rnTemp.Value
If varArray(lnIndex, 1) = strTemp Then
If WorksheetFunction.CountIf(rnTemp.EntireRow, varArray(lnIndex, 2)) = 0 Then 'Check if value exists already
Sheet1.Cells(rnTemp.Row, rnTemp.EntireRow.Columns.Count).End(xlToLeft).Offset(0, 1).Value =
varArray(lnIndex, 2)
End If
End If
Next Next
End Sub
