Hi Max,

Thanks for extend your help again.

Maybe I make it clear as my sheet X contains of :

Date Time Room Event
02/08/06 0900-1200 101 ABC
03/08/06 0900-1200 102 ABC
02/08/06 0900-1200 101 EFG
04/08/06 0900-1200 101 EFG

the duplicate data will be at row 1 and 3 although the event title was
different.

I need to consolidate the total hours for use or room 101. Thus I need to
delete one of the data at row 1 or row 3 to get the exact hour for usage of
room 101.

Do I need to write a macro on this action?

Regards
"Max" wrote:

"PL" wrote:
That's really help to capture the duplicate data. thanks! :)

Glad to hear that !

May I know is that another way to do within the same worksheet, for example
sheet x below, to delete the duplicate datas once identified?

I'll presume you mean extract the list of unique lines directly in an area
below in the source sheet: X instead of in a new sheet: Y. Try this construct
in the sample file's sheet X ..

In X,

Assume the source data in cols A to C is expected within row2 to row100 (99
rows)

Put in D110:
=IF(COUNTA(A2:C2)<3,"",IF(SUMPRODUCT((A$2:A2=X!A2) *(B$2:B2=X!B2)*(C$2:C2=X!C2))1,"",ROW()))

Put in A110:
=IF(ROW(A1)COUNT($D$110:$D$208),"",INDEX(A$2:A$10 0,MATCH(SMALL($D$110:$D$208,ROW(A1)),$D$110:$D$208 ,0)))
Copy A110 to C110

Then select A110:D110, fill down by the corresponding 99 rows to D208. The
uniques list will appear within A100:C208, all neatly bunched at the top.
Adapt the ranges to suit the expected extents.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---