How to delete duplicate data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


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

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

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

---
 
Back
Top