find duplicates in different sheets

[mrkasten]

I am using Excel 2010 and I need to find a way to find duplicated items that are in different worksheets. One sheet is a long list of none cashed checks and each month I get a new list and I need to find a way to see if any of the checks on the original list have been cashed. The checks that have been cashed will not be on the new sheet that I get every month.

 

[Comfy]

Re: find dublicates in different sheets

you could just do a vlookup between the old sheet and the new.

Any Results that are #N/A will be the cashed cheques.

 

[mrkasten]

Re: find dublicates in different sheets

It seems that I am having a problem with it because I am getting more #N/A's than I feel like i should. There is a 64 cell count difference between the 2 sheets but I am getting over 200 #N/A. On a few I have gone back to the new sheet and checked if some of the people that have #N/A are on that sheet and the number comes up.

 

[Comfy]

Re: find dublicates in different sheets

Could you post an example of an #N/A where the value is on both sheets?

and the Vlookup you are using.

I would imagine it's either a string formatting problem or your vlookup range isnt long enough

 

[mrkasten]

Re: find dublicates in different sheets

I believe I have found the problem. Some of the information had been moved to another sheet. I have taken care of the problem and everything appears to be working fine. Thank you for the help

 

[mrkasten]

Re: find dublicates in different sheets

Is there anyway to have rows with #N/A automatically removed?

 

[Comfy]

Re: find dublicates in different sheets

Yes. You can either use a filter or put this all into some VB and have the rows deleted permanently.

 

[mrkasten]

Re: find dublicates in different sheets

Could i create a macro to delete them and if so what would it look like. I have never used macros before. If that is to complicated i was wondering if there is also a way to automatically highlight those rows that come up with #N/A.
Thanks again for all of your help so far

 

[mrkasten]

Re: find dublicates in different sheets

Ive been looking up more about VB and I feel that it would be the best way to accomplish what I need. I have never used it before and was wondering if someone could help come up with the needed code to accomplish the tasks listed above including the vlookup

 

[Comfy]

Certainly.

Could you confirm whether:

the Ranges of your data would be dynamic? (amount of columns changes from sheet to sheet and whether the Cheque number column will move? from column A to B etc)

or are these fixed, so you will have the same number of columns and the cheque numbers will always be in column A for example.

Thanks,
/Comfy