How to delete duplicate data

P

PL

Hi,

I am using excel to consolidate monthly room booking data. I have a date
column and time column.

May I know how to to delete those rows which contains duplicate data with
same date stated in the date columnand and same time range in the time column?

Thank you.
 
P

PL

Dear Peo,

it seems not work. May I know does a macro helps?

I need to capture those duplicate data with the conditions of:
Same date, same time, same room

and either delete is or move to a spreadsheet.

Regards
 
M

Max

PL said:
.. need to capture those duplicate data with the conditions of:
Same date, same time, same room
Click to expand...

Here's a non-array formulas set-up which can dynamically drive out either a
list of unique lines, or a list of the duplicate lines from the source data.
The former -- a list of unique lines -- is perhaps the more important list.

Assume source data is in sheet: X,
cols A to C, data from row2 down, eg:

Date Time Room
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100
03-09-2006 9am-6pm 102
02-09-2006 9am-6pm 105

Extracting a list of unique lines from X

In a new sheet: Y,

Paste the same col headers into B1:D1 :
Date, Time, Room

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

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
Copy B2 to D2

Then just select A2:D2 and copy down to cover the max expected extent of
source data in X. Format col B as dates. Hide away col A or mask the font in
white. Cols B to D will return the uniques list dynamically from X, with all
results neatly bunched at the top, viz:

Date Time Room
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100

If we want to extract the list of duplicate lines from X instead,
just tweak the criteria formula in A2 to:
=IF(COUNTA(X!A2:C2)<3,"",IF(SUMPRODUCT((X!$A$2:A2=X!A2)*(X!$B$2:B2=X!B2)*(X!$C$2:C2=X!C2))>1,ROW(),""))
and copy A2 down. Rest of construct remains unchanged.
[just swap the ROW() and "" returns around in the 2nd IF]

For the sample data, we'd then get:

Date Time Room
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100
03-09-2006 9am-6pm 102
02-09-2006 9am-6pm 105
 
P

PL

Hi Max,

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

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?

Regards

Max said:
PL said:
.. need to capture those duplicate data with the conditions of:
Same date, same time, same room
Click to expand...

Here's a non-array formulas set-up which can dynamically drive out either a
list of unique lines, or a list of the duplicate lines from the source data.
The former -- a list of unique lines -- is perhaps the more important list.

Assume source data is in sheet: X,
cols A to C, data from row2 down, eg:

Date Time Room
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100
03-09-2006 9am-6pm 102
02-09-2006 9am-6pm 105

Extracting a list of unique lines from X

In a new sheet: Y,

Paste the same col headers into B1:D1 :
Date, Time, Room

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

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL($A:$A,ROW(A1)),$A:$A,0)))
Copy B2 to D2

Then just select A2:D2 and copy down to cover the max expected extent of
source data in X. Format col B as dates. Hide away col A or mask the font in
white. Cols B to D will return the uniques list dynamically from X, with all
results neatly bunched at the top, viz:

Date Time Room
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100

If we want to extract the list of duplicate lines from X instead,
just tweak the criteria formula in A2 to:
=IF(COUNTA(X!A2:C2)<3,"",IF(SUMPRODUCT((X!$A$2:A2=X!A2)*(X!$B$2:B2=X!B2)*(X!$C$2:C2=X!C2))>1,ROW(),""))
and copy A2 down. Rest of construct remains unchanged.
[just swap the ROW() and "" returns around in the 2nd IF]

For the sample data, we'd then get:

Date Time Room
02-09-2006 9am-6pm 105
03-09-2006 9am-6pm 102
04-09-2006 9am-6pm 100
03-09-2006 9am-6pm 102
02-09-2006 9am-6pm 105
Click to expand...
 
M

Max

PL said:
That's really help to capture the duplicate data. thanks! :)
Click to expand...

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?
Click to expand...

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$100,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.
 
P

PL

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
 
M

Max

PL said:
..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.
Click to expand...

But isn't that already achieved -- a list of unique lines -- neatly &
dynamically in either of the earlier suggestions using formulas? It's cleaner
to drag the unique lines out in a new sheet (Sheet Y's construct in the
sample). You could always refer to the derived sheet Y as-is for whatever
downstreams. Or you could, if desired, take a static snapshot of Y with an
entire sheet copy, then paste special as values/formats on another sheet.
Do I need to write a macro on this action?
Click to expand...

Suggest you try a post in .programming that's the option you really want.
I'm not proficient enough in vba to offer a solution here, sorry.
 
M

Max

.. 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.
Click to expand...

Just a clarification that "uniques" are treated as the first occurences from
the top row down in the formula construct. Duplicates would be those
identified further down which have the same "date-time-room" characteristic
as any preceding first occurence lines above it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell data entering slow! 0
Need Help with a VBA subroutine 0
Duplicate entry in a column/row 14
How can I copy data produced by a formula, not the formula? 2
Computing Elapsed Time 4
Query the same table three times 0
Help needed for inserting a row. 0
How to find duplicate column (not the cell values) 3

Top