| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Code
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Code
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB Code
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 "Shon" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating excel file, adding code to it from code, VBE window stays | Excel Programming | |||
| How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating my code? | Excel Programming | |||
| Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
| Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
| Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
Linear Mode
