BleepingComputer.com: Excel 2003 Data Compare Help

I have a workbook with two sheets of data. One sheet has the Research Data. The other sheet has the Database Data. I need to make sure that the data in both sheets are the same. The problem is that the Database Data has a lot more info than the Research Data. I just need to make sure what I have for Research Data matches in the Database Data. Below are the images of each sheet (if someone can tell me how to attach a file I am sure that will be easier).

Does anybody have any idea on how to make sure that everything from Research Data matches Database Data all in one sheet? If you could post a response spreadsheet that would be awesome. I want to do it in a separate sheet in the same workbook because there is so much data. The examples provided here are very small scale.

Research Data:


Database Data:


Sorry if I was confusing please let me know if you have an questions or confusion.

Thanks in advance,
Classic22

This post has been edited by classic22: 07 June 2011 - 01:29 PM

VLOOKUP() function will do much for the job. At least it can mark the rows that match. MATCH() and INDEX() are another way, a bit harder to do.
But you need something in common between the two sheets. Vlookup relies on a common element and you specify which column you want to match.
Basically you would ask excel to find in a bigger, sorted on ID table and entry from a smaller table that matches that ID.

You do have few common columns, good enough.
But in column A you have some ID of 101+ in one and 200001 in another.
If they "logically" correspond, and expression might work, but that's a flaky and risky proposition when we don't know your data.

Are you looking for something like 105 matches 200009 (they don't, I'm going for a concept) because values in the 3 columns on research match corresponding values on database in row 20009?

So, you need to tell a little more, also tell us -
What do you expect to see in the Compare sheet? Make up an example.
Do you need a separate Compare sheet or would a column on the sheet containing more rows be sufficient to indicate what got matched?
Is the number of rows identical in both sheets and the only difference is more columns on the Database sheet? Or is one sheet a big thing, and the other one a bunch of selected things?

This post has been edited by tos226: 07 June 2011 - 08:42 PM

I apologize for the confusion. On the Database Data sheet, the numbers in column A were supposed to be 101-120 as well (below is a corrected image) - I do know that they correspond to each other. Since I need to make sure everything that is found in the Research Data sheet matches up to everything in the Database Data sheet, I need to make sure each cell matches, so I need to see that each cell matches. To see that each cell matches the Compare sheet would need to be separate as there is way too much data in the real Research and Database Data sheets in my opinion. The number of rows in the Research and Database Data sheets are identical. Below is an example of what the current Compare sheet to looks like. Is there any way to attach the workbook to the post so it is easier to see whats going on in the cells?

Research Data:


Corrected Database Data:


Current Compare Sheet:


In the cells of the current Compare sheet:
-Cell A3-A23 ='Research Data'!A1 - ='Research Data'!A23
-Cell B3-B23 ='Research Data'!B1 - ='Research Data'!B23

-Cell D3-D23 ='Database Data'!A1 - ='Database Data'!A23
-Cell E3-E23 ='Database Data'!F1 - ='Database Data'!F23

-Cell G/H3 =B3=E3 Resulting "TRUE" means they match.
-Cell G4-G23 =ISNUMBER(MATCH(A4,$D$4:$D$23,0))- =ISNUMBER(MATCH(A23,$D$4:$D$23,0)) This is just a double check to ensure my Sample Names are the same. Since each cell returns "TRUE" they match.
-Cell H4-H23 =VLOOKUP($A4,$D$4:$E$23,2,0)=B4 - =VLOOKUP($A23,$D$4:$E$23,2,0)=B23 This is the part that actually tells me my data matches from each sheet on one specific column. Since each cell returns "TRUE" they match.

If cells in G4-G23 or H4-H23 did not match they would return "FALSE".

The issue is that I have to find the column from the Database Data Sheet (like ds2082 ='Database Data'!F1) that matches up to the Research Data column (like ds2082 ='Research Data'!B1), and enter the new column into E3 of the Compare sheet. Since the Research Data sheet is the limiting factor I can just go down the column sequentially in terms of entering the data into B3 of the Compare sheet. Since Database Data sheet has the columns out of order, I must first manually find where it is and then enter the data and then check if they match. This process becomes quite long seeing as there are >100 columns on the actual Research Data sheet and >1,000 columns on the actual Database Data sheet.

What I want is something that compares the data Research Data sheet with the data in the Database Data sheet all at once. This means the Compare sheet would use a function to find all the matching columns in the Database Data sheet to all the column in the Research Data sheet, so I wouldn't have to do it manually for each column and all the columns could be checked for matches simultaneously. To reword: I want E3 of the Compare sheet to automatically find the matching column dependent upon what B3 of the compare sheet is presenting. So what I want the new Compare sheet to look like doesn't really matter as long as I can make sure the data matches and it would do the finding work for me.

I hope that cleared things up? Let me know if it doesn't!

Thanks,
Classic22

1. If you use Vlookup() to find a matching row (as you now do), AND
2. if you use Hlookup() on the database row to find a matching field name from the research header row and it wouldn't hurt to name these areas,
THEN
3. you have the coordinates to compare the values at the intersection of found row and column from 1 and 2 above and no sorting or anything needed.
That still leaves you with 1000 columns to look at, though

4. a composite of all results might offer a answer that something is not matched

5. I have no idea how I'd handle 1000 columns. I think my excel has 256 columns and even that would be a nightmare to look at.
6. Ever thought of putting it all into a relational database such as Access and make reports based on queries to select only few things of interest?
7. Finally, take a shot at excel database functions, they do exist, and might help design something more managable.

8. In excel your other good tools after doing comparisons is Autofilter as well as Advanced filter - they're on the Data in Tools menu, IIRC.

This post has been edited by tos226: 15 June 2011 - 08:01 PM

Hey tos226

Thanks for your advice! I finally figured out how'd to do it. I didn't mean a 1,000 columns that was typo, I meant >100 haha. Below is the solution if you or anyone else is interested.



-Cell A1-A21 ='Research Data'!A1 - ='Research Data'!A21
-Cell A1-D3 ='Research Data'!A1 - ='Research Data'!D1
-Cell A2 =EXACT('Research Data'!B2,HLOOKUP(Compare!B$1,'Database Data'!$1:$65536,ROW(B2),FALSE)) - Just copied this formula down to A21 and then to the side to D21.

Thanks,
Classic22