I am currently working on an excel workbook that is pulling in a list of roughly 1000 reports going to a bunch of different emails. It is setup as Sheet: "ReportValues" Column A being Report Titles and Columns B-Q having all the different emails that the reports are sent to. I am trying to make a new sheet that lists the emails and reports they receive. Currently it is setup as Sheet: "Emails" Column A being the list of emails, Column B verifying if the emails are still in the contacts group, with the hopes of setting up Column C-Z as a listing of reports that email receives. If it is just a list of reports going to the email in one column that would work fine as well. I have tried a few different formulas but for the most part they have been unsuccessful. One side note that may help with this is that on the "ReportValues" table, some of the columns that should contain emails are empty because some reports only go to five emails while some go to seven and others go to three.
This may be simple to some but I am unexperienced with formulas in Excel so any help is greatly appreciated
My first try: "`=INDEX(ReportValues!$B$1:$Q$1000,QUOTIENT(ROW(A2)-1,16)+1,MOD(ROW(A2)-1,16)+1)` which returns a 0 even with B1 of ReportValues having an email in the box.
My second try: `=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!B$2:B$1000,0))` which gave me the first report for the email in Column A but wouldn't work other than giving the first report it found.
My Third try: `=IFERROR( SMALL( IF( $A$2:$A$1000 = TRANSPOSE(ReportValues!$B$2:$Q$1000), 1 + ROW( $A$2:$A$1000 ) - ROW($A$2 ), "" ), ROWS($2:2 ) ), "")` which returned a 1.
Example of ReportValues Table:
Column A | Column B | Column C | Column D |
Report Title | Dest Detail 1 | Dest Detail 2 | Dest Detail 3 |
Report One | Emailone@email.com | Emailtwo@email.com | |
Report Two | Emailtwo@email.com | | |
Report Three | Emailthree@email.com | Emailone@email.com | Emailfour@email.com |
Example of Emails Table:
Column A | Column B | Column C | Column D |
Email List | Still Employeed? | Report Title | Report Title 2 |
Emailone@email.com | True | Report One | Report Three |
Emailtwo@email.com | True | Report One | Report Two |
Emailthree@email.com | False | Report Three | |
Emailfour@email.com | True | Report Three | |
This may be simple to some but I am unexperienced with formulas in Excel so any help is greatly appreciated
My first try: "`=INDEX(ReportValues!$B$1:$Q$1000,QUOTIENT(ROW(A2)-1,16)+1,MOD(ROW(A2)-1,16)+1)` which returns a 0 even with B1 of ReportValues having an email in the box.
My second try: `=INDEX(ReportValues!$A$2:$A$1000,MATCH(Emails!$A3,ReportValues!B$2:B$1000,0))` which gave me the first report for the email in Column A but wouldn't work other than giving the first report it found.
My Third try: `=IFERROR( SMALL( IF( $A$2:$A$1000 = TRANSPOSE(ReportValues!$B$2:$Q$1000), 1 + ROW( $A$2:$A$1000 ) - ROW($A$2 ), "" ), ROWS($2:2 ) ), "")` which returned a 1.
Example of ReportValues Table:
Column A | Column B | Column C | Column D |
Report Title | Dest Detail 1 | Dest Detail 2 | Dest Detail 3 |
Report One | Emailone@email.com | Emailtwo@email.com | |
Report Two | Emailtwo@email.com | | |
Report Three | Emailthree@email.com | Emailone@email.com | Emailfour@email.com |
Example of Emails Table:
Column A | Column B | Column C | Column D |
Email List | Still Employeed? | Report Title | Report Title 2 |
Emailone@email.com | True | Report One | Report Three |
Emailtwo@email.com | True | Report One | Report Two |
Emailthree@email.com | False | Report Three | |
Emailfour@email.com | True | Report Three | |
