Can a macro or something list missing and duplicated data on a seperate sheet.

Hi

I have a wokbook with 3 sheets in it.

Sheets called "TAB" and "WFD" and "EXCEPTIONS"

What im trying to do is a macro or something that can do the following.

1) look in sheet "TAB" and compare against data in sheet called "WFD".
If data in sheet "TAB" and not "WFD" I need the line of data copied and pasted into the sheet called "EXCEPTIONS" and marked "MISSING FROM WFD"


2) look in sheet "WFD" and compare against data in sheet called "TAB".
If data in sheet "WFD" and not "TAB" I need the line of data copied and pasted into the sheet called "EXCEPTIONS" and marked "MISSING FROM TAB "

Some people may think this is the same as the other way round but there are occasions when data does go into WFD sheet by mistake as this data is manually input and data in tab sheet downloaded.

3) Look in sheet called "WFD" and look for duplicated entries and again copy and paste the duplicated line into the sheet called "EXCEPTIONS" and mark as "DUPLICATED"


When I say compare data there is data in columns A-E in all tabs and I need it to compare data across the columns on all rows.

Can this be done?

add a column (C) to Tab - add =Countif(A2,WFD!A:A)
add a column (C) to WFD =Countif(A2,TAB!A:A)

then add another column (D) to TAB =if(C1=0,"",CountA($C$1:$C1)) then all the noted lines should be numbered then in WFD =if(C1=0,"",CountA($C$1:$C1)+Max(TAB!D:D)) - then WFD will continue numbering the exceptions

On the exception tab - column A should be numbered 1-x...
column B =if(Countif(A1,TAB!D:D)>0,Index(Tab!A:B,Match(A1,Tab!D:D,0),1),If(Countif(A1,WFD!D:D)>0,Index(WFD!A:B,Match(A1,WFD!D:D),"")

This will add the first column - change 1 to 2 for the second column Once you get to the last - change the Index Match to "Missing from WFD" or missing from TAB

Thanks for that but im a bit confused on the last bit.

Where you said

On the exception tab - column A should be numbered 1-x...
column B =if(Countif(A1,TAB!D:D)>0,Index(Tab!A:B,Match(A1,Tab!D:D,0),1),If(Countif(A1,WFD!D:D)>0,Index(WFD!A:B,Match(A1,WFD!D:D),"")

This will add the first column - change 1 to 2 for the second column Once you get to the last - change the Index Match to "Missing from WFD" or missing from TAB

Ive copied and pasted into column B the formula but what do you mean by column A?

you need a numbered column 1-x
the formulas on the other tabs should number the missing units

then column b (or next column) checks to see if the number from the first column is on sheet Tab (contif) if it is use index match to bring it over if it is not on sheet Tab - it checks sheet WFD (Countif) if it is it uses Index Match to bring it over - if it is not on either sheet it leaves blank ("")

The match function declares the Row that the unit is found on - the number after Match is the column -- so to bring over the 2nd column you would change the one to 2 etc...

If you post your worksheet I will show you