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:

Property IDs	Licensing Condition
ABC	123
BCD	234
CDE	345
DEF	456
EFG	567

<tbody>
</tbody>

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

Licensing Condition	Item IDs
123	Item 1
123	Item 2
123	Item 1
123	Item 3
234	Item 2
234	Item 3
234	Item 4
456	Item 1
456	Item 1

<tbody>
</tbody>

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

Property ID	Item ID
ABC	Item 3
ABC	Item 2
Abc	Item 2
abc	Item 1
bcd	Item 4
BCD	Item 4
DEF	Item 4
ABC	Item 2
EFG	Item 2
EFG	Item 3
BCD	Item 1
CdE	Item 3

<tbody>
</tbody>

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:

Property IDs	Licensing Condition	Has an unconditional interaction occurred?
ABC	123	Y
BCD	234	N
CDE	345	N
DEF	456	N
EFG	567	Y

<tbody>
</tbody>

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.