how to filter list based on multiple criteria

[JSBPL]

Date S/No Name Dept R/No
26/07/09 01 ABC M1 M001
26/07/09 02 DEF S1 S001
26/07/09 03 GHI A1 A001
27/07/09 04 DEF S1 S001
27/07/09 05 ABC M1 M001
27/07/09 06 GHI A1 A002

I have a long list of records as shown above, how can I copy the list to
another range with records for "Name", "Dept" and "R/No" not duplicated and
showing the latest S/No. In other words, Record 1 and Record 2 should not
appear as it is duplicated in Record 5 and Record 4 respectively.

The new list should only shows:

Date S/No Name Dept R/No
26/07/09 03 GHI A1 A001
27/07/09 04 DEF S1 S001
27/07/09 05 ABC M1 M001
27/07/09 06 GHI A1 A002

Thanks for any help i can get.

 

[Max]

Here's one play which delivers the new list that you seek

Assume the source table you posted is in A1:E7
In G2
=IF(COUNTA(C2:E2)<3,"",IF(SUMPRODUCT((C2:C$7=C2)*(D2$7=D2)*(E2:E$7=E2))>1,"",ROW()))
Leave G1 empty. This is the criteria col. It basically nails down your
definition of duplicates (from bottom-up).

In H2:
=IF(ROWS($1:1)>COUNT($G:$G),"",INDEX(A:A,SMALL($G:$G,ROWS($1:1))))
Copy H2 to L2. Select G2:L2, copy down to L7. Format col H as dates.
Minimize/hide col G. Cols H to L will return the outputs that you seek.

Success? Click the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik