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