Comparing Values on 2 Spreadsheets

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':

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!!!!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
In the response spreadsheet can you parse the data in column e to columns e - j? What I mean is, is it possible to do or is there data in those columns as well?
 
Upvote 0
Hi,

In the response spreadsheet it's possible to parse the data in column e to columns e-j....but the data can be of any length!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top