Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Complarisons and lookup 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.
  #2   Report Post  
Senior Member
 
Posts: 663
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.
Could you post some sample data so we can see your data layout?
It will make it far easier to provide a working solution that way.
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
lookup needed to give value where the lookup value is a string within a cell. Christopher Flack Excel Worksheet Functions 1 July 24th 12 08:50 AM
Vertical lookup with a lookup value that returns multiple matches andreashermle Excel Programming 3 June 15th 10 12:39 AM
lookup help. lookup result based on data in 2 columns lcc Excel Worksheet Functions 3 April 6th 10 01:20 PM
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU JCC Excel Discussion (Misc queries) 5 June 26th 09 09:15 PM
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup JBush Excel Worksheet Functions 3 January 4th 07 12:14 AM


All times are GMT +1. The time now is 10:08 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"