Compare multiple items across multiple worksheets

[uceaamh]

I am attempting to compare multiple items against multiple worksheets, to find out if certain property IDs are interacting with the appropriate item IDs, dependent on a licensing condition.

Examples:

In Worksheet 1, I have a pivot identifying all the unique property IDs. I use a pivot because each property ID will appear multiple times in the data.
That pivot also identifies the unique licensing condition against each property ID. It looks like:

[TABLE="class: grid, width: 236"]
<tbody>[TR]
[TD]Property IDs[/TD]
[TD]Licensing Condition[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]234[/TD]
[/TR]
[TR]
[TD]CDE[/TD]
[TD="align: right"]345[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD="align: right"]456[/TD]
[/TR]
[TR]
[TD]EFG[/TD]
[TD="align: right"]567[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2 has the licensing condition information. Each license lists allowable Item IDs. Unfortunately, it does so individually, and may repeat, like so:

[TABLE="class: grid, width: 236"]
<tbody>[TR]
[TD]Licensing Condition[/TD]
[TD]Item IDs[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD="align: right"]234[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD="align: right"]234[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD="align: right"]234[/TD]
[TD]Item 4[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD]Item 1[/TD]
[/TR]
</tbody>[/TABLE]

And finally, on the third sheet, I have a data set which has Property IDs, and which Item IDs they interacted with:

[TABLE="class: grid, width: 236"]
<tbody>[TR]
[TD]Property ID[/TD]
[TD]Item ID[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]Abc[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD]bcd[/TD]
[TD]Item 4[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]Item 4[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]Item 4[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]EFG[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]EFG[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD]CdE[/TD]
[TD]Item 3[/TD]
[/TR]
</tbody>[/TABLE]


My end goal is to identify, per property ID, if there has been an interaction with an Item ID not allowable according to the licensing condition.

To end product would ideally look like:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Property IDs[/TD]
[TD]Licensing Condition[/TD]
[TD]Has an unconditional interaction occurred?[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]123[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD="align: right"]234[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]CDE[/TD]
[TD="align: right"]345[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD="align: right"]456[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]EFG[/TD]
[TD="align: right"]567[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]


I'm not sure where to start with this. I've tried:

=SUMPRODUCT(([@[Wksheet 1 Property ID]]=Wksheet 2 [Property ID])*([@[Wksheet 1 Property ID]]=Wksheet 3 [Property ID])*(Wksheet 2 [Item ID]=Wksheet 3[Item ID])*(1))

But that didn't work, and I don't think it was the appropriate route to go down.

Any help is greatly appreciated, and thank you for taking the time to read this far!

 

[Fazza]

hello uceaamh

What about a query to identify the ID with unconditional interaction?

With three source worksheets : Sheet1, Sheet2 and Sheet3 each with a simple table of data (so not a pivot table on Sheet1, just a simple table), query

SELECT DISTINCT C.[Property ID]
FROM [Sheet3$] C LEFT OUTER JOIN 
(SELECT DISTINCT A.[Property IDs], B.[Item IDs]
FROM [Sheet1$] A INNER JOIN  [Sheet2$] B ON A.[Licensing Condition] = B.[Licensing Condition]) D ON C.[Property ID] = D.[Property IDs]
WHERE D.[Item IDs] Is Null

For the sample data this returned three ID : BCD, CDE & EFG

BTW, either I've misunderstood or the Y/N results back to front in the posted sample. Not that it matters, it is a Boolean/binary return. One or the other. And I think it gives the correct result wrt that.

Here is a good description of how to set up a query, https://analystcave.com/create-microsoft-query-excel-excel-query/
which is refreshable like a query table.

regards, Fazza

 

[uceaamh]

Sorry for the late reply.

Thanks for responding, am trying to give that a go now. Will report in with results.