| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding muliple duplicate records in spreadsheet
Advanced filter isn't the answer (I think)
Duplicate Row issue 5 Columns -- ECode, JCode, ECode, PPHrs, PPDate. I will sometimes have several entries (updates) to the ECode, PPDate, and PPHrs for some records. I need to compare the five columns for duplicates and post the records with the greatest hours for the ECode, ECode, and PPDate ECode JCode ECode PPHrs PPDate 54511 003690 027 1 01/09/09 54511 003690 027 3.5 01/09/09 54511 003690 027 11 01/09/09 54511 003690 027 16 01/23/09 54511 003690 027 8 02/20/09 54511 003690 027 8 02/20/09 54511 003690 027 12 02/20/09 54511 003690 027 16 03/06/09 54511 003690 027 8 03/20/09 54511 003690 027 16 03/20/09 54511 003690 027 40 03/20/09 54511 003690 027 5 04/03/09 54511 003690 027 5 05/01/09 54511 003690 032 8 03/20/09 54511 003690 837 24 02/06/09 I need to keep the highlighted rows and delete the other rows €“ or copy them to another worksheet. The spreadsheet has about 16000 rows and 12 columns, but these are the columns I need to work with.. Thanks in advance.. (PC, xcel 03) ShagNasty€¦ |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding muliple duplicate records in spreadsheet
When you tried advanced filter; what is going wrong..
DataFilterAdvanced FilterSelect Copy to another location..Select the list range, Mention 'copy to' as (say cell G1). Check "Unique Records only'..OK If this post helps click Yes --------------- Jacob Skaria "ShagNasty" wrote: Advanced filter isn't the answer (I think) Duplicate Row issue 5 Columns -- ECode, JCode, ECode, PPHrs, PPDate. I will sometimes have several entries (updates) to the ECode, PPDate, and PPHrs for some records. I need to compare the five columns for duplicates and post the records with the greatest hours for the ECode, ECode, and PPDate ECode JCode ECode PPHrs PPDate 54511 003690 027 1 01/09/09 54511 003690 027 3.5 01/09/09 54511 003690 027 11 01/09/09 54511 003690 027 16 01/23/09 54511 003690 027 8 02/20/09 54511 003690 027 8 02/20/09 54511 003690 027 12 02/20/09 54511 003690 027 16 03/06/09 54511 003690 027 8 03/20/09 54511 003690 027 16 03/20/09 54511 003690 027 40 03/20/09 54511 003690 027 5 04/03/09 54511 003690 027 5 05/01/09 54511 003690 032 8 03/20/09 54511 003690 837 24 02/06/09 I need to keep the highlighted rows and delete the other rows €“ or copy them to another worksheet. The spreadsheet has about 16000 rows and 12 columns, but these are the columns I need to work with.. Thanks in advance.. (PC, xcel 03) ShagNasty€¦ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding muliple duplicate records in spreadsheet
Oops.. Ignore the previous post..I didnt notice the 'greatest hours'
Try the below 1. First thing is to change the column header so that there are no duplicates. Now there are two Ecodes. 2. Assuming your data is from Col A to E, cut and paste Col D (hours) to the right. So that ECode, JCode, "X"Code, PPDate are together.. 3. Select the first four cellrange/columns. DataFilterAdvanced FilterSelect 'Copy to another location'. Mention 'copy to' as (say cell G1). Check "Unique Records only'..OK 4. Now you have a unique set of records in G:J columns... 5. Now use the below formula in K2 and copy that down. =MAX(IF((A2:A100=G2)*(B2:B100=H2)*(C2:C100=I2)*(D2 :D100=J2),E2:E100)) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "ShagNasty" wrote: Advanced filter isn't the answer (I think) Duplicate Row issue 5 Columns -- ECode, JCode, ECode, PPHrs, PPDate. I will sometimes have several entries (updates) to the ECode, PPDate, and PPHrs for some records. I need to compare the five columns for duplicates and post the records with the greatest hours for the ECode, ECode, and PPDate ECode JCode ECode PPHrs PPDate 54511 003690 027 1 01/09/09 54511 003690 027 3.5 01/09/09 54511 003690 027 11 01/09/09 54511 003690 027 16 01/23/09 54511 003690 027 8 02/20/09 54511 003690 027 8 02/20/09 54511 003690 027 12 02/20/09 54511 003690 027 16 03/06/09 54511 003690 027 8 03/20/09 54511 003690 027 16 03/20/09 54511 003690 027 40 03/20/09 54511 003690 027 5 04/03/09 54511 003690 027 5 05/01/09 54511 003690 032 8 03/20/09 54511 003690 837 24 02/06/09 I need to keep the highlighted rows and delete the other rows €“ or copy them to another worksheet. The spreadsheet has about 16000 rows and 12 columns, but these are the columns I need to work with.. Thanks in advance.. (PC, xcel 03) ShagNasty€¦ |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding muliple duplicate records in spreadsheet
Change to absolute reference
=MAX(IF(($A$2:$A$16000=G2)*($B$2:$B$16000=H2)*($C$ 2:$C$16000=I2)*($D$2:$D$16000=J2),$E$2:$E$16000)) -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Oops.. Ignore the previous post..I didnt notice the 'greatest hours' Try the below 1. First thing is to change the column header so that there are no duplicates. Now there are two Ecodes. 2. Assuming your data is from Col A to E, cut and paste Col D (hours) to the right. So that ECode, JCode, "X"Code, PPDate are together.. 3. Select the first four cellrange/columns. DataFilterAdvanced FilterSelect 'Copy to another location'. Mention 'copy to' as (say cell G1). Check "Unique Records only'..OK 4. Now you have a unique set of records in G:J columns... 5. Now use the below formula in K2 and copy that down. =MAX(IF((A2:A100=G2)*(B2:B100=H2)*(C2:C100=I2)*(D2 :D100=J2),E2:E100)) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "ShagNasty" wrote: Advanced filter isn't the answer (I think) Duplicate Row issue 5 Columns -- ECode, JCode, ECode, PPHrs, PPDate. I will sometimes have several entries (updates) to the ECode, PPDate, and PPHrs for some records. I need to compare the five columns for duplicates and post the records with the greatest hours for the ECode, ECode, and PPDate ECode JCode ECode PPHrs PPDate 54511 003690 027 1 01/09/09 54511 003690 027 3.5 01/09/09 54511 003690 027 11 01/09/09 54511 003690 027 16 01/23/09 54511 003690 027 8 02/20/09 54511 003690 027 8 02/20/09 54511 003690 027 12 02/20/09 54511 003690 027 16 03/06/09 54511 003690 027 8 03/20/09 54511 003690 027 16 03/20/09 54511 003690 027 40 03/20/09 54511 003690 027 5 04/03/09 54511 003690 027 5 05/01/09 54511 003690 032 8 03/20/09 54511 003690 837 24 02/06/09 I need to keep the highlighted rows and delete the other rows €“ or copy them to another worksheet. The spreadsheet has about 16000 rows and 12 columns, but these are the columns I need to work with.. Thanks in advance.. (PC, xcel 03) ShagNasty€¦ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding muliple duplicate records in spreadsheet
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding duplicate records that are not exact matches but very clos | Excel Discussion (Misc queries) | |||
| Finding duplicate records | Excel Worksheet Functions | |||
| Finding duplicate numbers in a spreadsheet | Excel Worksheet Functions | |||
| Finding duplicate records in multiple worksheets | Excel Discussion (Misc queries) | |||
| Finding duplicate records in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
