I am in over my head a bit on this one, and could use some assistance. It would be most appreciated.
I have Worksheet 1 that tracks travel by Name and Document Number. The report already utilizes several formulas to pull in relevant data (dates, amounts, etc.) by these two variables from a report on Worksheet 2. This report is updated by copying and pasting into Worksheet 2 with data derived from another source. When this overlay occurs, all the fields on Worksheet 1 update with the new data that corresponds to the Name/Document number combo.
Column A = Names Already Listed in Existing Worksheet
Column B = Document #s Already Listed in Existing Worksheet
Column C = Names Listed In Updated Report
Column D = Document #s Listed in Updated Report
For simplicity sake I've listed these all together, however, Columns A and B actually appear on on Worksheet 1, and Columns C and D appear on Worksheet 2 where we copy and paste over the data from another source to update the listing. Column E referenced below will appear on Worksheet 1 and is the focus of this question (and perhaps the need for a column F?).
As a test, in Column E, I used the formula {=IFERROR(INDEX($A$1:$A$1999,MATCH(0,IFERROR(MATCH($A$1:$A$1999,$C$1:$C$1999,0),COUNTIF($E$1:$E1,$A$1:$A$1999)),0)),"")} and have been able to return a list of names that are on the updated report, that are not yet programmed into the Existing Worksheet 1, which indicates to the user that they would need to manually add that name to the worksheet so that it will update. Once they have done so, the name dissappears from Column E.
What I am needing, due to the fact that there may be duplicate Names, with different Document Numbers, is a formula that will Compare A to C AND B to D and return in E (and perhaps F?) those combos of names/document numbers that do not appear in the existing Worksheet 1.
An example would be:
James Smith already appears in our Worksheet 1, with a Document Number of R7-000001. This updates automatically for all other variables on the worksheet when that combo is found on Worksheet 2. When James Smith appears in combination with R7-000002, I need the new formula to list that combination so the user knows it needs to be added to Worksheet 1 for tracking.
Thanks in advance for any guidance.
Bookmarks