If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how do I compare columns in two different workbooks?
I need to compare the information in one column of an excel workbook to
another column of an excel workbook. Then need the system to report match or non-match. Thanks |
#2
|
|||
|
|||
What exactly do you mean by a match? For example:
If we are using cells A1:A10 and B1:B10, would be a match if the number '5' appeared in A1 and B5? Or, would the number '5' have to appear in A1 and B1 to constitute a match? If the match can be anywhere in the second column, this might do it: =IF(ISNA(VLOOKUP(A1,$B$1:$B$10,1,0)),"No Match","Match") If the match has to be in the same location: =IF(A1=B1,"Match","No Match") In both cases, copy down as far as needed. tj "Provider98122" wrote: I need to compare the information in one column of an excel workbook to another column of an excel workbook. Then need the system to report match or non-match. Thanks |
#3
|
|||
|
|||
The title of your message got cut off on my machine--tghe methods I posted
won't work from one file to another. Sorry about that. tj "Provider98122" wrote: I need to compare the information in one column of an excel workbook to another column of an excel workbook. Then need the system to report match or non-match. Thanks |
#4
|
|||
|
|||
I am doing something where I am comparing Column S to Column A. I used the
formula you provided, but I don't want it to say "Match" or "No Match". I want it to say whatever is in the cell (the date). What would I do to make it return the value of the cell? Here's my modified version: =IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0) ),"No Match","Match") Also, if the date does match, I want to return columns T-AI on Sheet1 in columns T-AI on Sheet2 that correspond to the cell that it matched. Example: Sheet1 Columns A B C ......... S T U 10/1 R Y 9/29 R G 10/2 G G 10/2 R R Sheet2 would return: Columns A B C ......... S T U 10/2 R R Thanks, Sharon "tjtjjtjt" wrote: What exactly do you mean by a match? For example: If we are using cells A1:A10 and B1:B10, would be a match if the number '5' appeared in A1 and B5? Or, would the number '5' have to appear in A1 and B1 to constitute a match? If the match can be anywhere in the second column, this might do it: =IF(ISNA(VLOOKUP(A1,$B$1:$B$10,1,0)),"No Match","Match") If the match has to be in the same location: =IF(A1=B1,"Match","No Match") In both cases, copy down as far as needed. tj "Provider98122" wrote: I need to compare the information in one column of an excel workbook to another column of an excel workbook. Then need the system to report match or non-match. Thanks |
#5
|
|||
|
|||
I'm not sure I understand what you are asking. If the below options don't
help you get the answer you are looking for, please post back with step by step instructions as to what you are looking for. It's time for me to retire for the evening, but I will check back tomorrow to make sure you ulitimately received a satisfactory answer. If you want to check for a match and return the date only if there is a match: =IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0) ),"No Match",VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0)) **I typed this formula here manually. If it malfunctions, it's probably because I left out a parentheses** As for the sheet2 and sheet1 issue, I don't know what cell in column T you started with, so I assumed T3 only because it matches S3 in the other formula: If you want to return Columns T-AI only if the value in Column S matches any value in Column S, then put this in T3 on Sheet2: IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0)) ,"",Sheet1!T3) Copy this over to AI. Copy down as far as needed as well. Here is where I'm a bit confused with your post: I am doing something where I am comparing Column S to Column A. I used the formula you provided, but I don't want it to say "Match" or "No Match". I want it to say whatever is in the cell (the date). What would I do to make it return the value of the cell? What do you mean by 'whatever is in the cell'? You want to return some value even if there isn't a match? If so, the value from what cell? To be clear, can S3 match any value in A3:A400? Or does it have to match A3? The answer to this question may make a different approach to the one I am taking a better option. tj |
#6
|
|||
|
|||
Thanks for your response. I will try to implement what you've suggested so
far and see if it works. Sorry for the confusion. By "whatever" I meant whatever value is in the cell, should be returned. It might say "red", "green", "yellow", "no", or it might contain a date. Also, the matching that I'm wanting to accomplish is looking for a date in column S and if it is anywhere in Column A, then return the date value from column s on sheet 2. The corresponding columns T-AI would be for the same row as where the date came from in Column S. I hope that makes sense. I'll post again in a new thread tomorrow if I can't get it to work. Thanks again, Sharon "tjtjjtjt" wrote: I'm not sure I understand what you are asking. If the below options don't help you get the answer you are looking for, please post back with step by step instructions as to what you are looking for. It's time for me to retire for the evening, but I will check back tomorrow to make sure you ulitimately received a satisfactory answer. If you want to check for a match and return the date only if there is a match: =IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0) ),"No Match",VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0)) **I typed this formula here manually. If it malfunctions, it's probably because I left out a parentheses** As for the sheet2 and sheet1 issue, I don't know what cell in column T you started with, so I assumed T3 only because it matches S3 in the other formula: If you want to return Columns T-AI only if the value in Column S matches any value in Column S, then put this in T3 on Sheet2: IF(ISNA(VLOOKUP(Sheet1!S3,Sheet1!$A$3:$A$400,1,0)) ,"",Sheet1!T3) Copy this over to AI. Copy down as far as needed as well. Here is where I'm a bit confused with your post: I am doing something where I am comparing Column S to Column A. I used the formula you provided, but I don't want it to say "Match" or "No Match". I want it to say whatever is in the cell (the date). What would I do to make it return the value of the cell? What do you mean by 'whatever is in the cell'? You want to return some value even if there isn't a match? If so, the value from what cell? To be clear, can S3 match any value in A3:A400? Or does it have to match A3? The answer to this question may make a different approach to the one I am taking a better option. tj |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to compare two columns of data for duplicates | Sandie | Worksheet Functions | 5 | August 13th, 2004 03:13 PM |
compare two columns using a function called EXACT | Kimberly | Worksheet Functions | 1 | April 7th, 2004 08:35 PM |
comparing data and return only data that exsist in the 2 columns | victorwill | Worksheet Functions | 2 | December 27th, 2003 12:13 AM |
Help: Compare two columns | CHR | Links and Linking | 1 | October 29th, 2003 10:50 PM |
How to compare Multiple Rows and Columns to get data | Lee Li [MSFT] | Setting up and Configuration | 0 | September 15th, 2003 09:09 AM |