Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
compare 2 excel documents
- Thread starter FeS2
- Start date
chrisaroundtown
Technical User
I am sure sure if this will help but here goes.
You can do a count of cells in doc 2 that match a particular cell in doc 1. In other words, if cell B1 contain the text "Orange", you can put a formula in cell C1 to tell you how many times "Orange" appears in doc 2 column A. If orange does not appear at all you will get 0.
In excel doc 1 insert an empty column c, right next to column b. In cell C1 put this formula =countif('[ExcelDoc1.xls]Sheet1'!A1 Then copy this formula down column C for all the cells in column B. Any cells showing zero don't appear in column A of doc 2.
Let me know if this helps.
Thanks
Chris
You can do a count of cells in doc 2 that match a particular cell in doc 1. In other words, if cell B1 contain the text "Orange", you can put a formula in cell C1 to tell you how many times "Orange" appears in doc 2 column A. If orange does not appear at all you will get 0.
In excel doc 1 insert an empty column c, right next to column b. In cell C1 put this formula =countif('[ExcelDoc1.xls]Sheet1'!A1 Then copy this formula down column C for all the cells in column B. Any cells showing zero don't appear in column A of doc 2.
Let me know if this helps.
Thanks
Chris
When I have to compare data between 2 sheets, I use the Vlookup function and return a value from the 2nd sheet if a value from the first matches one on the 2nd...
If the first cell you are wanting to bring back a match is A1, go to the first blank column to the right.
go to the functions and chose Vlookup
your lookup_value will be the column you want to see if there is a match on sheet 2 (column A) A:A
tab to Table_array
Your table_array will be what column you want to look for the match on the other sheet...if you are looking in column B, highlight column B and C (or whatever value you wish to return upon a match)
tab to col_index_num
If you want to return value from column C you would count your columns from B to C which would be 2 so put 2 here.
tab and type in false.
Hit ok..
go back to sheet 1 and drag or copy this formula down the length of your data. Everytime there is a match on sheet 2 for the values on sheet 1, a value (from column C in this example) cooresponding to that match will be returned.
Not sure if this will help or if I explained it well enough but I have found it a very useful tool when comparing sheets with one another.
Keden
If the first cell you are wanting to bring back a match is A1, go to the first blank column to the right.
go to the functions and chose Vlookup
your lookup_value will be the column you want to see if there is a match on sheet 2 (column A) A:A
tab to Table_array
Your table_array will be what column you want to look for the match on the other sheet...if you are looking in column B, highlight column B and C (or whatever value you wish to return upon a match)
tab to col_index_num
If you want to return value from column C you would count your columns from B to C which would be 2 so put 2 here.
tab and type in false.
Hit ok..
go back to sheet 1 and drag or copy this formula down the length of your data. Everytime there is a match on sheet 2 for the values on sheet 1, a value (from column C in this example) cooresponding to that match will be returned.
Not sure if this will help or if I explained it well enough but I have found it a very useful tool when comparing sheets with one another.
Keden
- Thread starter
- #4
- Thread starter
- #5
Thanks for everyones help. With your help I have gotten this figured out, used if statement in empty cell next to the cell I wanted to verify and it compaired that cell with evrything in column B on the second workbook and returned the value of cell B1 if it found a match.
=IF(B1=[Book2]Sheet1!$B:$B,B1)
=IF(B1=[Book2]Sheet1!$B:$B,B1)
- Status
Similar threads
- Locked
- Question
- Question