comparing two files

T

tracktraining

Hi All,

is it possible to compare the data in two files and copy and paste the rows
that are not found in either file to a different spreadsheet or highlight the
rows or something to make them stand out?

Thanks in advance for the help.

Thanks,
Tracktraining
 
J

Joel

Please re-read your posting. I don't think you want to find data that "IS
NOT FOUND IN EITHER FILE". I think you mean "NOT FOUND IN ONE OF THE FILES".
the method varies depending on how many columns you are comparing.

For comparing one column in each file adding an auxilarry column with a
formula works nicely. Use Vlookup in new auxillary column.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

=Vlookup(A1,Book2.xls!A1:A1000,1,False)

An N/A will be returned if not found. You can remove the N/A using an IF
statement

=if(ISNA(Vlookup(A1,Book2.xls!A1:A1000,1,False)),"Not found","Found")

You can the remove the formula by by copying the auxilarry column and using
PASDTESPECIAL with Value checked to get either "Not found" or "Found". Then
sort to get athe Not found items and paste into a new sheet. Then repeat
process in 2nd worksheet to get the complete list of items found in only one
sheet.
 
J

Jim Cone

My commercial Excel add-in "XL Companion" will do that.
Its Match Rows feature returns the uncommon rows between two worksheets.
Email me direct and ask for the free trial version.
Remove xxx from my email address... james.coneXXX at comcast.netXXX
Please provide your real name and geographic location.
--
Jim Cone
Portland, Oregon USA


"tracktraining" <[email protected]>
wrote in message
Hi All,
is it possible to compare the data in two files and copy and paste the rows
that are not found in either file to a different spreadsheet or highlight the
rows or something to make them stand out?
Thanks in advance for the help.
Thanks,
Tracktraining
 
Top