Excel VBA vlookup有多个结果 [英] Excel VBA vlookup with multiple results
问题描述
我在A列中有一个报告列表，这些报告转到B列中的电子邮件列表。列B有1个电子邮件和10个之间的任意值，用逗号，分隔。我需要一个Vlookup或VBA代码来帮助我在新工作表中对列表进行排序，因为列A是每个单独的电子邮件（已经完成），然后是每个电子邮件在B列中收到的报告。我遇到的问题是那里大约有250个报告，其中一些具有相同的名称，B列中大约有125封电子邮件，但主要是不同行中重复的电子邮件。我需要它作为列A：电子邮件列C：报告，以便我可以开始查看什么电子邮件获取什么报告，以便我可以开始修剪报告/合并类似等等。列B由另一个VBA功能采取，验证电子邮件仍然存在在域联系人组中。如何获得这份收到报告的电子邮件列表？
当前工作表名称是原始数据：ReportsQuery以及我放置数据的位置：电子邮件。
excel表格示例：
I have a list of reports in Column A that go to a list of emails in Column B. Column B has anywhere between 1 email and 10 separated by a comma ",". I need a Vlookup or VBA code to help me sort the list in a new sheet as Column A being each individual email (already done) and then the report(s) each email receives in Column B. The problem I run into is that there is roughly 250 reports, some with the same name and roughly 125 emails in Column B but mainly of the emails repeating in different rows. I need it as Column A: Emails Column C: Reports so that I can start to see what email is getting what report so I can start trimming reports/merging similar, etc. Column B is taken by another VBA function verifying that emails still exist in the domain contact group. How can I achieve this list of emails getting reports?
The current sheets names are Original Data: "ReportsQuery" and where I am putting data: "Emails".
Example of excel sheet:
Report Title | Emails
Report One | Emailone@email.com,Emailtwo@email.com
Report Two | Emailone@email.com,Emailthree@email.com,emailfour@email.com
Report Three | Emailfive@email.com,Emailone@email.com,emailsix@email.com
Report Four | Emailseven@email.com
我的尝试：
我只尝试了Vlookup公式，但它会报告一个报告而不是多个报告。还试过
What I have tried:
I tried just Vlookup formulas but it would report one report and not multiple. Also tried
=INDEX(ReportValues!$A$2:$Q$1000,QUOTIENT(ROW(A2)-2,16)+1,MOD(ROW(A2)-2,16)+1)这也给了我一份报告。
试过：
which also gives me one report.
Tried:
=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
WITH VALUES：
WITH VALUES:
=IFERROR(INDEX(ReportQuery!$A$2:$A$1000, SMALL(IF($A2 = ReportQuery!$B$2:$B$1000, ROW(ReportQuery!$A$2:$A$1000) - 1, ""), COLUMN() - 3)), "")
但由于Lookup电子邮件的值不仅仅是o，因此返回空白ne email。
but it returns blank due to the Lookup email value having more than just that one email.
推荐答案
A
2：
Q
这篇关于Excel VBA vlookup有多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
