Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Complex comparisons help!

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.

Last edited by Nullform : June 2nd 13 at 04:34 AM
  #2   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Nullform View Post
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.

Sorry if I posted this in the wrong section.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
complex color fill conditions- if statements or complex formula? lilly8008 Excel Discussion (Misc queries) 1 December 18th 09 05:57 AM
Yr to Yr comparisons cisse_5 Excel Discussion (Misc queries) 7 May 15th 09 06:10 PM
Pairwise comparisons? Donal P[_2_] Excel Discussion (Misc queries) 1 December 8th 08 08:40 PM
Multiply IF AND OR comparisons Hannu Laine Excel Worksheet Functions 1 June 16th 07 10:14 PM
string comparisons Ron Excel Discussion (Misc queries) 0 February 17th 05 06:02 PM


All times are GMT +1. The time now is 11:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"