Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
PL PL is offline
external usenet poster
 
Posts: 58
Default How to delete duplicate data

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.



  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 299
Default How to delete duplicate data

Datafilteradvanced filter, unique records only and copy to another
location

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"PL" wrote in message
...
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.





  #3   Report Post  
Posted to microsoft.public.excel.newusers
PL PL is offline
external usenet poster
 
Posts: 58
Default How to delete duplicate data

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

"Peo Sjoblom" wrote:

Datafilteradvanced filter, unique records only and copy to another
location

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"PL" wrote in message
...
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.






  #4   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to delete duplicate data

"PL" wrote:
.. need to capture those duplicate data with the conditions of:
Same date, same time, same room


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(SMAL L($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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #5   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to delete duplicate data

A sample implementation is available at:
http://cjoint.com/?iFkkDSKkXk
Dynamic uniques or duplicates listing on multiple conditions.xls
(X - source data, Y - Uniques listing, Z - Duplicates listing)
[ Link is good for 14 days ]
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #6   Report Post  
Posted to microsoft.public.excel.newusers
PL PL is offline
external usenet poster
 
Posts: 58
Default How to delete duplicate data

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" wrote:

"PL" wrote:
.. need to capture those duplicate data with the conditions of:
Same date, same time, same room


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(SMAL L($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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

  #7   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to delete duplicate data

"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
---
  #8   Report Post  
Posted to microsoft.public.excel.newusers
PL PL is offline
external usenet poster
 
Posts: 58
Default How to delete duplicate data

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

  #9   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to delete duplicate data

"PL" wrote:
..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.


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?


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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #10   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to delete duplicate data

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


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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I delete all rows that contain no red highlighted data? Linda Hudock Excel Discussion (Misc queries) 7 May 22nd 06 09:09 AM
Data values WON'T DELETE in PivotTable drop-downs Natalie 1229 Excel Discussion (Misc queries) 3 March 27th 06 09:18 PM
Delete rows with no data Mindie Setting up and Configuration of Excel 1 November 30th 05 10:24 PM
Unique and duplicate data between 2 Excel worksheets Greg Excel Worksheet Functions 0 August 9th 05 12:03 AM
How do I find duplicate rows in a list in Excel, and not delete it Matthew in FL Excel Discussion (Misc queries) 2 June 15th 05 09:11 PM


All times are GMT +1. The time now is 06:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"