Thanks for your help on this. That does work for all those records that have
duplicates however there are some records which have no duplicates it is just
a single record and the formula for these also returns a 1. I would want to
keep these records and not delete them. Any ideas?



"Peter T" wrote:

If I follow probably easier without code -
Assuming "2nd Item Number" is in col-B and Location is in col-i place the
following formula in a helper column in row 2 (for me it's in K2

=SUMPRODUCT(($B$2:$B$11=B2)*($I$2:$I$11=I2))

Copy the formula down
in K1, ie cell above the formula cell type some label
Filter K1 greater or equal : 2

You could copy the filtered range elsewhere. Alternatively do Filter equals
: 1 and delete the visible rows

If that approach seems to work, Name the ranges in col-B & col-i, perhaps
extend way below to cater for future data. Replace the references in the
formula with the names.

Regards,
Peter T



"Shon" wrote in message
...
I have a spreadsheet with 20000 records in. Following is just a small snap
shot of some of the data

Business Unit 2nd Item Number Trans QTY Do Ty Document Number G/L Date
Unit
Cost Location QOH
4050 45061EAA7197EAEA 3 OV 447943 10/06/2008 187.00 HBU 1
4050 45061EAA7197EAEA 3 OV 447943 10/06/2008 187.00 4G5 1
4050 45061EAA7197EAEA 3 OV 447943 10/06/2008 187.00 HBU 1
4050 45065EAA5000EAEA 56 OV 513251 07/08/2009 15.74 T6F 6
4050 45065EAA5000EAEA 4 OV 512387 31/07/2009 17.31 T6F 6
4050 45065EAA5000EAEA 7 OV 512388 31/07/2009 17.31 GH4 6
4050 45065EAA5000EAEA 1 OV 512389 31/07/2009 17.31 T6F 6
4050 44089EAA4000EAEA 2 OV 447262 06/06/2008 12.99 AD4 6
4050 44089EAA4000EAEA 5 OV 363699 15/03/2007 11.95 AS3 6
4050 44089EAA4000EAEA 1 OV 446036 30/05/2008 12.29 AD4 8

I want to be able to create a macro that looks down the list of data with
the rule that where the 2nd item number and location are the same in
multiple
records, then keep those records and delete the others. I'm basically
trying
to do the opposite of the Remove Duplicates function. I would expect to
see
the following results;

Business Unit 2nd Item Number Trans QTY Do Ty Document Number G/L Date
Unit
Cost Location QOH
4050 45061EAA7197EAEA 3 OV 447943 10/06/2008 187.00 HBU 1
4050 45061EAA7197EAEA 3 OV 447943 10/06/2008 187.00 HBU 1
4050 45065EAA5000EAEA 56 OV 513251 07/08/2009 15.74 T6F 6
4050 45065EAA5000EAEA 1 OV 512389 31/07/2009 17.31 T6F 6
4050 44089EAA4000EAEA 2 OV 447262 06/06/2008 12.99 AD4 6
4050 44089EAA4000EAEA 1 OV 446036 30/05/2008 12.29 AD4 8



.