Check For Duplicates Over Several Sheets

[MikeLanglois]

Good morning all,


We currently have an excel document that is used by three people, who work on three seperate sheets. It is a shared document, so it updates each time the user saves with the changes from the other users.


One thing we are having a problem with is duplicate information. What we would need is some way of checking if any other user has entered in the same information as another user.


For example, user 1 inputs 000000123 in J15 and saves. (the column will always be J, the row may be different) User 2 puts 0000000123 in J48 and saves. This then changes cell color to red to show duplicates (or any other conditional formatting, preferably a cell color change though.)


I imagine this is possible with VBA, however I am not 100% certain, can anyone help?

 

[NARAYANK991]

Hi Mike ,


It is certainly possible , using just formulae.


Let us assume you have 3 named ranges as follows : Sheet1_ColJ , Sheet2_ColJ and Sheet3_ColJ , where Sheet1 can refer to any worksheet tab. Suppose you have 3 sheets named User 1 , User 2 and User 3.


So the named range Sheet1_ColJ can refer to ='User 1'!$J:$J ; similarly create the other named ranges.


Now select the entire column J in the sheet User 1 , and enter the following CF formula , and select a Fill color RED :


=AND($J1<>"",OR(ISNUMBER(MATCH($J1,Sheet2_ColJ,0)),ISNUMBER(MATCH($J1,Sheet3_ColJ,0))))


Now , when ever any data is entered in the sheet User 1 , if it exists on any of the other 2 sheets , the cell in which this data has been entered will turn RED.


Narayan