Finding muliple duplicate records in spreadsheet

[ShagNasty]

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…

 

[Jacob Skaria]

When you tried advanced filter; what is going wrong..

Data>Filter>Advanced Filter>Select 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]

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. Data>Filter>Advanced
Filter>Select '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)*(D2100=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]

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))

 

[Bernd P]

Hello,

Maybe my UDF Pstat can help you here:
http://www.sulprobil.com/html/pstat.html

Regards,
Bernd