augustjade
New Member
- Joined
- Sep 14, 2006
- Messages
- 10
Hi,
Hopefully someone is able to help me since I've been working on this problem for several days!
I have two spreadsheets named 'Responses' and 'Department'. The Responses spreadsheet consists of column A (which contains a TeamID) and column E (which contains each team's response).
Here's a sample of how 'Responses' looks:
A1: TeamID
A2: 12
A3: 4532
A4: 33
E1: Response
E2: 1324, 5555, 2569, 3096, 8
E3: 5 6 2456
E4: 9, 0, 5, 2, 6, 3, 7, 10, 33
The number of TeamIDs can be up to 1000+ and their responses can be either comma or space delimited and of any length. The values in columns A and E are supplied by team members.
The Department spreadsheet lists the TeamID and what the correct response should be for that team. The response is spread over 5 columns in the same row. Here's a sample of 'Department':
There can be up to 500+ Team IDs in this spreadsheet.
Questions
1. Is there a formula or macro I can use that will:
- take the value of a TeamID in the 'Department' spreadsheet, check if it exists in the 'Responses' spreadsheet;
- if it exists, compare the correct response in columns B-F of the Department spreadsheet to the response supplied in the 'Responses' spreadsheet
- State 'Yes' in the Match column
The problem I'm facing is that the correct responses are in individual columns (and in any order) in 'Department' and the user supplied response is in one cell (either comma or space delimited)
2. Is there anyway to have column H in 'Department' list what values were not supplied by the user?
For example for TeamID 4532, column H would show 0, 0 since it wasn't supplied by Team 4532 on the 'Responses' page.
Any help would be great since I'm finding Excel formulas/macros really difficult to understand.
Thanks!!!!
Hopefully someone is able to help me since I've been working on this problem for several days!
I have two spreadsheets named 'Responses' and 'Department'. The Responses spreadsheet consists of column A (which contains a TeamID) and column E (which contains each team's response).
Here's a sample of how 'Responses' looks:
A1: TeamID
A2: 12
A3: 4532
A4: 33
E1: Response
E2: 1324, 5555, 2569, 3096, 8
E3: 5 6 2456
E4: 9, 0, 5, 2, 6, 3, 7, 10, 33
The number of TeamIDs can be up to 1000+ and their responses can be either comma or space delimited and of any length. The values in columns A and E are supplied by team members.
The Department spreadsheet lists the TeamID and what the correct response should be for that team. The response is spread over 5 columns in the same row. Here's a sample of 'Department':
Code:
A B C D E F G H
Team ID Auth# Auth# Auth# Auth# Auth# Match Missing
22 5 0 33 2 4 No
4532 5 0 2456 6 0 Yes 0, 0
There can be up to 500+ Team IDs in this spreadsheet.
Questions
1. Is there a formula or macro I can use that will:
- take the value of a TeamID in the 'Department' spreadsheet, check if it exists in the 'Responses' spreadsheet;
- if it exists, compare the correct response in columns B-F of the Department spreadsheet to the response supplied in the 'Responses' spreadsheet
- State 'Yes' in the Match column
The problem I'm facing is that the correct responses are in individual columns (and in any order) in 'Department' and the user supplied response is in one cell (either comma or space delimited)
2. Is there anyway to have column H in 'Department' list what values were not supplied by the user?
For example for TeamID 4532, column H would show 0, 0 since it wasn't supplied by Team 4532 on the 'Responses' page.
Any help would be great since I'm finding Excel formulas/macros really difficult to understand.
Thanks!!!!