VB Code

[Shon]

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

 

[Peter T]

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]

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]

In your OP you said "then keep those records and delete the others" ie keep
all duplicates and delete non duplicates, or as you said "the opposite of
the Remove Duplicates function"

The formula will return a value of 1, no duplicates, or larger if
duplicated. Applying the filter does not delete any records, it will either
hide rows with all the duplicates (= 2 or more) or the non duplicates (= 1).
It's up to you if you choose to delete say the visible non-duplicates, or
copy either the filtered duplicates or non duplicates elsewhere.

Another thing you might look at is the Advanced filter with a view to
copying duplicates or non-duplicates elsewhere in one go.

If I'm misunderstanding something post back with clear details of the
overall objective.

Regards,
Peter T

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.