Complex lookup and compare help!

[Nullform]

So I'm having an issue with creating a formula to make an "easy to read" chart. Here is the data I have to work with

300 some odd "sites", about 500+ doors and about 12+ badges for each door. each site has a set of data. In each set of data there are two fields. The first field is a door number and the second field is a badge number. Some doors can be opened with multiple badges.

I have my chart set up with all the doors and all the badge numbers at the top and all the sites listed on the left, each in it's own row, like this.

Door 1 | Door 1 | Door 1 | Door 1 | Door 2 | Door 2 | Door 3

Badge 1 |Badge2 |Badge3 |Badge 4|Badge1 |Badge 2| Badge4

Site Alpha
Site Bravo
Site Charlie


For each site, I want to display an X in the cell if that site has a particular door number and a badge number.

The problem I'm running into is that one door can have multiple badges to it so the formulas I've tried using arrays will find "door x" and "badge x" anywhere in the array I'm pulling from and put an X. So in the example below, since door 1 has a badge 3 in the array, it would put an X under door 2 badge 3 even though that door/badge doesn't exist at site Bravo.

Site Bravo
Door 1, Badge 1
Door 1, Badge 2
Door 1, Badge 3
Door 2, Badge 1
Door 2, Badge 4

I'm open to macros or whatever else I can use to get this job done without having to go through and manually put in thousands of Xs on the sheet.

Currently I have all the data both on one sheet and I have each site separated to it's own sheet, so whichever way works best.

[FDibbins]

Hi and welcome to the forum

Sounds to me like you need to add a helper row and combine the door/badge combo? You can then look up that specific combo.

[Nullform]

Thanks for the welcome!

I was thinking about that but I was trying to get away without going through and adding another row for each of the 300 some odd sites. With all of the sites combined I have over 8000 entries that I would need to uniquely ID

[FDibbins]

no, If I understand you correctly, you dont need to do it for the sites, just fir the 1sr 2 rows...
Row 1 = Door1...door1...etc
Row 2 = Badge 1...badge 2...etc
Row 3 = Door1Baqdge1...Door1Badge2...etc

[Nullform]

I'm not sure if I'm following. Even if I unique ID'd the master list then I'm not sure how I would have it correspond to each site's data.

Here's a picture to help illustrate what I'm after.

http://www.nullform.com/pics/excelhelp.jpg

[FDibbins]

upload the workbook, not a picture of it please

[Nullform]

workbook is located at www.nullform.com/uploads/BadgeBreakdown.xlsm

the "master" sheet has all of the site's doors and badges in two long columns. Each of the sheets beginning with "data" is the same list of sites, just each site broken out into it's own sheet.