Script to recognise types of duplicate data

Hey all,

I basically need to take the data from Sheet1 and split it into Sheet2 and Sheet3 based on some duplication criteria

Sheet1 Column B has a list of productID numbers some of which are unique and some of which are duplicate because they have a variant. The variant number is in Column C. Column D is productCost which contains either a value or #N/A.

Where the productID is unique I need to change the #N/A in column D to DELETE-THIS-RECORD.

Where the productID has duplicates and all the variantIDs have a productCost of #N/A I also need to change those to DELETE-THIS-RECORD and put into Sheet2 with a new header

Where the productID has duplicates but not all the variantIDs have a productCost of #N/A I need to change those to -1 and put into Sheet3 with a new header.

All rows that have a value instead of #N/A in column D are not to be changed or copied to any other sheet and the row can actually be deleted.

I have attached my example file which has the source data in Sheet1 and the data the way I want it in Sheet2 and Sheet3.

Hope I've made sense, its a little complicated to explain but it should be clear in my Example.xls file.

Any help would be much appretiated.
Thanks
Dave

try the macro, of course I offsetted the results to see if they performed well..

Now it deletes the records with values in column D

Wow thank you so much. It works an absolute treat, even with thousands of lines of data.

Thanks
Dave

you are very welcome... anything else?

Yes please, what do I change in the code so that the columns are not offset?

Cheers
Dave

just take off the +5 in every m+5 instance

easy

Thanks again