Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete all rows that contain no red highlighted data? | Excel Discussion (Misc queries) | |||
Data values WON'T DELETE in PivotTable drop-downs | Excel Discussion (Misc queries) | |||
Delete rows with no data | Setting up and Configuration of Excel | |||
Unique and duplicate data between 2 Excel worksheets | Excel Worksheet Functions | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) |