I am creating a worksheet that compares reports against each other. The goal of this worksheet is to find duplicate identification numbers that are showing up on more than one report, so that only one person/report has to address the issue(s) with that ID number, not all of the people/reports that it becomes connected to.
This process will save an immense amount of time in our office.

I have the majority of the macro codes figured out for this report, but this one is troubling me.

Once we have searched for the Previous Day duplicates and the same day duplicates, and determined which duplicates are from different reports, we need to code column A to identify WHICH report should have responsibility of that ID number, based on a hierarchy of which reports the number is on.

Column A: where I want the Report Name the report should be assigned to
Column B: "Diff. Report" (result of formula- Y for yes, on multiple reports, blank for No...presorted at this point to only have Ys)
Column C: "PDD or SDD", Prevous day or Same Day duplicates (result of formula- sorted at this point to only have SDDs that have Ys in column B)
Column D: Hierarchy code (but not necessary for this comparison- just for visual reference)
Column E: Report name...from a list of: DA, DF, DL, DQ, DP, DI, MB, QM, BF, IA, RI. (Identifies which report the number is found on).
Column F: Identification Number.

Essentially, I want to have a formula that runs something like this:
If Column F cells match, look at Column E...based on the hierarchy (which is on a list on Sheet 2), code into Column A the higher report name in both/all cells (of matching F rows).
The hierarchy list is as follows: (BF, IA, RI), DL, DQ, DP, DA, DF, DI, MB, QM
The list in parentheses is the exception...if the number is on one of these 3 reports, it muts be worked by those reports AND the other report (unless there are more than 2 reports for that ID number and one report is in the parentheses, in which case it is worked by that report and THEN gone through the hierarchy formula).

Any ideas would be EXTREMELY appreciated. I've been working on this for a while and haven't come up with any great ideas of how to go about this. Thanks!