Double Look-up with Duplicate Values

[mattgunner25]

​I am attempting to create a macro (mainly just recording my work) for a fairly complex data audit process.

I have three data sets that need to be used for comparison. Each one contains a list of product compositions.

So here is the process I need to run through (did an example below)

Look-up which SpecID's in the first data set are used as components in the second data set. This is tricky, since a SpecID in the first data set (11* codes) will most likely be attached to multiple SpecID's (17* codes) in the second data set.

After all of the 17* codes that use the first 11* codes are identified, we move on to the third data set. Here, we must compare the component ID's of the 17* codes to its original 11* codes, as well at the % of the component and component type.

So below, SpecID's 11254 is in two 17* SpecID's (17753 & 17699)

We must take those two 17* Specs, and check their component ID's in the third data set. Below, it shows that the 17753 has one incorrect component (11016, which is not included in the original 11254 SpecID component list). If this component % is below 2, then it is ok, however, it is shown at 8%, so it is an error.

So, when we run into that error, we need to flag it for the audit (simply highlighting in red will suffice).

What is the best way to compare the accuracy of these three data sets, starting with our 11* SpecID's and moving downstream to their corresponding 17* SpecID compositions?

I don't believe a series of Vlookups would work since there are multiple 17* codes attached to the 11* codes. Think of the 11* codes as the parents and the 17* as its children-- a bunch of 17* codes will be made up entirely out of the 11* codes---so each 17* code that falls under an 11*'s umbrella must contain all the same components of the original 11* at greater than 2%, without any extras, as well as maintain the same component type.

I think it is easy to highlight the errors, however, I am having difficulty matching all the 11*'s up to each of the 17*'s due to all the repeating values. Is there some lookup function that makes this possible? The reports will vary in length (each data sets will have a different number of rows)?



First data set: Pr Comp- contains repeating column A values for each component ID value it has (both contain two components)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Spec ID[/TD]
[TD]Component ID[/TD]
[TD]Comp Type[/TD]
[/TR]
[TR]
[TD]11254[/TD]
[TD]11050[/TD]
[TD]Monomer[/TD]
[/TR]
[TR]
[TD]11254[/TD]
[TD]11112[/TD]
[TD]Monomer[/TD]
[/TR]
[TR]
[TD]11255[/TD]
[TD]11484[/TD]
[TD]Monomer[/TD]
[/TR]
[TR]
[TD]11255[/TD]
[TD]11392[/TD]
[TD]Active-Agent[/TD]
[/TR]
</tbody>[/TABLE]

Second Data Set: PS-Exact Comp- Provides link between Spec Id of Pr Comp and Spec Id of PS Monomer Comp

[TABLE="width: 500"]
<tbody>[TR]
[TD]Spec ID-2[/TD]
[TD]Component ID[/TD]
[/TR]
[TR]
[TD]17753[/TD]
[TD]11254[/TD]
[/TR]
[TR]
[TD]17699[/TD]
[TD]11254[/TD]
[/TR]
[TR]
[TD]17117[/TD]
[TD]11255[/TD]
[/TR]
[TR]
[TD]17238[/TD]
[TD]11255[/TD]
[/TR]
</tbody>[/TABLE]

Third Data Set: PS Monomer Comp- This is the data that must match the PR Component ID's in two ways
1. Each component of its matching specification ID (11254 for 17753) must be present at at greater than 2%
2. Each comp type must match (11050 must be a Monomer in each data set)[TABLE="width: 500"]
<tbody>[TR]
[TD]Spec ID-3[/TD]
[TD]Component ID[/TD]
[TD]Comp Type[/TD]
[TD]Percentage[/TD]
[/TR]
[TR]
[TD]17753[/TD]
[TD]11050[/TD]
[TD]Monomer[/TD]
[TD]92%[/TD]
[/TR]
[TR]
[TD]17753[/TD]
[TD]11016[/TD]
[TD]Active-Agent[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD]17699[/TD]
[TD]11112[/TD]
[TD]Monomer[/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD]17699[/TD]
[TD]11050[/TD]
[TD]Monomer[/TD]
[TD]52%[/TD]
[/TR]
[TR]
[TD]17117[/TD]
[TD]11255[/TD]
[TD]Monomer[/TD]
[TD]71%[/TD]
[/TR]
[TR]
[TD]17117[/TD]
[TD]11392[/TD]
[TD]Monomer[/TD]
[TD]29%[/TD]
[/TR]
</tbody>[/TABLE]


The errors were highlighted in red (11016 is an extra component with a percentage >2, and 11392 has an incorrect comp type)

So that is a lot of information, let me know if you have any questions with my request