Compare multiple items across multiple worksheets

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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 IDsLicensing Condition
ABC123
BCD234
CDE345
DEF456
EFG567

<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 ConditionItem IDs
123Item 1
123Item 2
123Item 1
123Item 3
234Item 2
234Item 3
234Item 4
456Item 1
456Item 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 IDItem ID
ABCItem 3
ABCItem 2
AbcItem 2
abcItem 1
bcdItem 4
BCDItem 4
DEFItem 4
ABCItem 2
EFGItem 2
EFGItem 3
BCDItem 1
CdEItem 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 IDsLicensing ConditionHas an unconditional interaction occurred?
ABC123Y
BCD234N
CDE345N
DEF456N
EFG567Y

<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!
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
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
Code:
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

New Member
Joined
Feb 23, 2017
Messages
27
Sorry for the late reply.

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

Forum statistics

Threads
1,078,240
Messages
5,339,034
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top