vba Vlookup 搜索非唯一值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26418146/
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
Vlookup to search non-unique values
提问by sk8er_boi47
I have 2 excel sheets with below values
我有 2 个具有以下值的 excel 表
Sheet1
表 1
EmpID AppName AppID [Only AppID is Unique]
Sheet2
表 2
EmpID AppName RequestNum [Only RequestNum is unique]
EmpIDis repeated since there are multiple apps associated with one users
AppName is repeated since there are multiple instances of single app associated to one users
EmpID重复，因为有多个应用程序与一个用户关联 AppName 重复，因为单个应用程序的多个实例与一个用户关联
Now, I want to get RequestNum from Sheet2 into Sheet1, but it obviously returns me the 1st value in case of duplicates. Concatnating EmpID & AppNamedoes not work since it does not make a unique combination. How do I achieve that? Would it be possible with combination of formulae or do I need a macro?
现在，我想从 中获取 RequestNum Sheet2 into Sheet1，但它显然会在重复的情况下返回第一个值。连接EmpID & AppName不起作用，因为它不会形成唯一的组合。我如何做到这一点？可以结合公式还是我需要一个宏？
Pasting the sample for my sheets below:
在下面粘贴我的工作表的示例：
Sheet1:
表 1：
EmpID AppName AppID
A123 App1 UniqueID001
A123 App2 UniqueID002
B444 App66 UniqueID003
B898 App1 UniqueID004
H123 App33 UniqueID005
A123 App1 UniqueID006
B444 App33 UniqueID007
L001 App2 UniqueID008
H123 App1 UniqueID009
Sheet2:
表2：
EmpID AppName RequestNum
A123 App1 UniqueReq001
A123 App2 UniqueReq002
B444 App66 UniqueReq003
B898 App1 UniqueReq004
H123 App33 UniqueReq005
A123 App1 UniqueReq006
B444 App33 UniqueReq007
L001 App2 UniqueReq008
H123 App1 UniqueReq009
A123 App1 UniqueReq010
A123 App2 UniqueReq011
B444 App66 UniqueReq012
B898 App1 UniqueReq013
H123 App33 UniqueReq014
A123 App1 UniqueReq015
B444 App33 UniqueReq016
L001 App2 UniqueReq017
H123 App1 UniqueReq018
回答by
If Sheet1's AppIDis unique and Sheet2's RequestNumis unique I'm not sure how you would determine which EmpID& AppNameto send Sheet2's RequestNumto but here is one possible solution.
如果 Sheet1 的AppID是唯一的，而 Sheet2 的RequestNum是唯一的，我不确定您将如何确定将 Sheet2 的RequestNum发送到哪个EmpID和AppName，但这是一种可能的解决方案。
???????
???????
The formula in D2 is =IFERROR(INDEX($H$2:$H$9,SMALL(INDEX(ROW($1:$8)+(($F$2:$F$9<>$A2)+($G$2:$G$9<>$B2))*1E+99,,),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2))),""). Fill down as necessary.
D2 中的公式是=IFERROR(INDEX($H$2:$H$9,SMALL(INDEX(ROW($1:$8)+(($F$2:$F$9<>$A2)+($G$2:$G$9<>$B2))*1E+99,,),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2))),"")。根据需要填写。
回答by sk8er_boi47
Ok i had this figured out, without any macro or complex formula...
好的，我已经弄清楚了，没有任何宏或复杂的公式......
- First i had to sort both the sheets in Emp ID & then on App Name
- Insert new column @ the begining & concatenate Emp ID + App Name
- Inserted 2 new columns after app name: say Count & Unique
- Under count i simply counted the repetations made in 1st Concatenated column using countif
- Under Unique in incremented the value for unique repeatations using if (=IF(A2=A1,C1+1,1)) --> This gives me unique (well..almost unique) values in both the sheet to vlookup on to
- 首先，我必须在 Emp ID 和 App Name 中对两个工作表进行排序
- 在开头插入新列并连接 Emp ID + App Name
- 在应用名称后插入 2 个新列：说 Count & Unique
- 在计数下，我只是使用 countif 计算了在第 1 个串联列中进行的重复次数
- 在 Unique in 下使用 if (=IF(A2=A1,C1+1,1)) --> 这给了我唯一的（好吧..几乎唯一的）值来查看到
Thx for all the help :)
感谢所有帮助:)
回答by user3616725
if I understand you correctly on sheet1 you want to display all "RequestNum" from Sheet2 that match "EmpID" and "AppName" on sheet one. There are going to be multiple "RequestNum"s, so I have concatenated the IDs into a string. Also note that multiple rows on Sheet1 will show the same "RequestNum_s", where "AppID" is defferent, but "EmpID" and "AppName" are the same.
如果我在 sheet1 上正确理解您，您希望在 Sheet1 上显示 Sheet2 中与“EmpID”和“AppName”匹配的所有“RequestNum”。将会有多个“RequestNum”，所以我将这些 ID 连接成一个字符串。另请注意，Sheet1 上的多行将显示相同的“RequestNum_s”，其中“AppID”不同，但“EmpID”和“AppName”相同。
formula for D2 on Sheet1:=join("; ",filter(Sheet2!$C$1:$C$19,Sheet2!$A$1:$A$19=A2,Sheet2!$B$1:$B$19=B2))
Sheet1 上 D2 的公式：=join("; ",filter(Sheet2!$C$1:$C$19,Sheet2!$A$1:$A$19=A2,Sheet2!$B$1:$B$19=B2))
copy down.
抄下来。
