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

Status
Not open for further replies.

FeS2

Technical User
Joined
Aug 16, 2002
Messages
82
Location
US
Please help, I am stumped on this one. I need to compare column B in excel doc 1 to column A in excel doc 2 and highlight the cells in doc 1 that don't match any of the cells in doc 2.
 
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
 
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
 
Sorry Chris, I tried but it didn't help, thank you.
 
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top