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.



0
PL1414 (9)
8/30/2006 10:47:01 PM
excel.newusers 15348 articles. 2 followers. Follow

9 Replies
487 Views

Similar Articles

[PageSpeed] 55

Data>filter>advanced 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" <PL@discussions.microsoft.com> wrote in message 
news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@microsoft.com...
> 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.
>
>
> 


0
Peo
8/30/2006 10:53:35 PM
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:

> Data>filter>advanced 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" <PL@discussions.microsoft.com> wrote in message 
> news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@microsoft.com...
> > 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.
> >
> >
> > 
> 
> 
> 
0
PL1414 (9)
8/31/2006 1:48:02 AM
"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(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

-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
8/31/2006 7:57:02 AM
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
---
0
demechanik (4694)
8/31/2006 8:16:02 AM
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(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
> 
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
0
PL1414 (9)
8/31/2006 10:08:02 AM
"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$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.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
8/31/2006 10:52:01 AM
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$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.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
0
PL1414 (9)
9/1/2006 1:04:01 AM
"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
---
0
demechanik (4694)
9/1/2006 2:37:01 AM
> .. 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
---
0
demechanik (4694)
9/1/2006 2:47:01 AM
Reply:

Similar Artilces:

Find MAX data in sheet (Cell)
How would one go about finding the cell that contains the MAX info, o say any/all cells that exceeds 8,000 characters/spaces in a Excel page -- confuzedagai ----------------------------------------------------------------------- confuzedagain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943 View this thread: http://www.excelforum.com/showthread.php?threadid=49148 1. Highlight the entire document 2. Select Format->Conditional Formatting 3. Select "Formula Is" 4. Input this formula =LEN(A1)>8000 5. Format with something obvious like green bac...

duplicates #3
Dear People, Does anyone know how to delete duplicate entries from excel? With conditional formatting it seems ezy enuf to find dupliates but how to delete them ... apart from one at a time that is. Ok when the list is short but when its long as your arm, that can seriously cut into ones drinking time. John One way .. Take the sample data below assumed in A2:A5: > Mark Davies > John Smith > David Jones > Mark Davies > David Jones > John Smith Put in B2: =IF(COUNTIF($A$2:A2,A2)>1,1,"") Copy down to B5 This'll mark any duplicates in col A with a &quo...

Export data in XML through Web Services
I have a WebMethod that retrive data from a DB and I want to return this data in a XML format. I try to do with the TextWriter class but I wasn't able to do, because I didn't found any example thet explain how to use the Stream attribute. Can I use the TextWriter or I have to use the XmlDocument class? Thanks in advance -------------------------------------------- Massimo Rizzotto -------------------------------------------- can you show us the code now, and show us what you are getting on the wire and show us what you would like to get on the wire? This article http://www.15secon...

OLXP AutoComplete: How to Delete Single Entry
Q289975 mentions "changing" a single entry, but not deleting it. Posts to this list on the same subject mention how to delete entries from the AutoComplete list, but those suggestions will not work to remove a single entry from the cache. Can anyone suggest a procedure to remove a single entry from the AutoComplete list that actually works? OLXP AutoComplete caches resolved addresses into the <profilename>.NK2 file. Deleting the NK2 file removes single instances but that is a rather extreme solution to the problem. Is there an editor that one can use to edit the NK2 file to re...

data labels in charts
This is a probably one of the simplest questions but I'm just starting to chart again. I have entered a category title "year" in cell A1 and entered from 1995 to 2000 from A2 to A7 that I want to appear on the horizontal axis. I have entered a title in B1(unemployed) and 5 values B2 to B7 directly below. When I choose a "column" chart my vertical axis appears correctly but the horizontal axis does not include the years as labels. The labels that appear are the numbers 1 to 6 instead of 1995 to 2000. How do I make the chart pick up the data labels from colu...

data extraction from excel
I need to pull information from one collumn. If the collumn is comprised of yes's and no's, can excel create a list of just the yes's? Try something like this: Assuming your list is in A1:B100, with A1: Question B1: Response C1: Response C2: Yes D1: Question (the same col heading as A1) E1: Response (the same col heading as B1) Select your list (A1:B100) Data>Filter>Advanced Filter List Range: (already selected) Criteria Range: C1:C2 Click the [OK] button to filter the list in place. OR To copy the matching records to another area: Click: Copy to another location Copy...

QUESTION- Simpler Way to Add a Data Series to Other Graphs?
Hi- I'm using Excel 2002 and I've got a lot of XY (Scatter) graphs. When I add a new data series to one graph, it is defined by 3 values (Name, X Value and Y Value) If I want to add this data series to 7 or 8 add'l graphs, I know that I can copy/paste these 3 values to each of them. Is there a simpler way to accomplish this? thnx ...

Deleting a word from a Cell
Hi Can anyone help, i am trying to deletel the last word from a Cell My cell currently read: - M04274059519 Total and I need a formula that will delete the "total" word Would really appreciate some help linexe On Jan 21, 7:27=A0am, Linexe <l.clark...@hotmail.co.uk> wrote: > Hi > > Can anyone help, i am trying to deletel the last word from a Cell > > My cell currently read: - =A0M04274059519 Total > > and I need a formula that will delete the "total" word > > Would really appreciate some help > > linexe C...

I'm sending 2 (duplicate) emails each time I send emai
I'm using Office 2007, Outlook and, each time I send an email.. the receiver gets two copies.... any thoughts? Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'd still be sufficiently protected by your on-access scanner part of the virus scanner. For more details see; http://www.msoutlook.info/question/20 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http:/...

pasting excel data in a powerpoint slide
I am attempting to paste an excel data table in a powerpoint slide but once pasted, the data is cut off. If I double click and enter the excel data table and resize the window from Powerpoint, the same data shown simply stretches to the new size. I have tried numerous ways of pasting from the excel sheet and it still acts the same. I tried a new workbook and was able to resize correctly. But to recreate all of the data and formulas in a different workbook would be difficult. Is there a setting or something in excel that is not allowing me to resize this particular workbook? Thanks...

many sheets from "master data"
Hi guys, In the last few days, ive posted some questions with some success, bu not being able to completely work though my problem. Given that I a novice/intermediate in relation to VBA, I am having extreme difficult augmenting the code to do what i like. Attached is the file so you can visualize what I am talking about. What I want to do is to put each "page" of data on a new slide. As yo can see, all the data is found on the first page. In column T, you ca see where each new page starts (as indicated by the data and pag number). I having trying tirelessly to get the code su...

Prevent equation from skipping data when referencing import data?
I am referencing data that is being imported from access in table on a seperate worksheet. When the data is refreshed, only the last new entry is placed in my seperate table, not all of them. How do I fix this? ...

Input formulas in a defined data range and convert results as valu
Hi, I have a dataset for users to view data. It has the following format till year 2010 and have portions of topics like forecast, shipment, aging .... and the whole dataset can go very long vertically. Within each topics is the type of products measured, below is an extracts of my dataset: Forecast Accuracy Jan 2007 Feb 2007 Mar 2007 Apr 2007 Prod A x x x x Prod B x x x x Prod C ...

Cannot delete resources from Project Server 2007
When trying to delete resources from Project Server 2007 using the Delete Enterprise Objects I receive the following error: The specified Resources and Users could not be deleted because of the following errors: The action was canceled by an event handler. We have Project Server 2007 and EPMLive installed and working together. ...

When deleting an email, it's so fast, it deletes the next email t.
I just started using Outlook 2003 (on a new computer at work). When deleting an email, it's so fast, it deletes the next email too. Is there anyway to change a setting so it doesn't automatically delete emails so fast. I end up undeleting every email... just so I can check to make sure I haven't accidentally deleted something I haven't read yet. I've looked at the mouse settings, but they are set on the slower end. HELP. Thanks, Kathy :) Kathy <Kathy@discussions.microsoft.com> wrote: > I just started using Outlook 2003 (on a new computer at work). When ...

2002 data merge
I have a database of 50 names that I would like to print out as individual labes on a standard 81/2" X 11" sheet of paper. I would like 10 labels per page and can work out the page layout in Publisher 2002. When I go to merge my Works database of the 50 names, I obviously only have one "field" to insert. As you can see where this is going, when I do my merge, I get a sheet with 10 labels of the same name. How do I get it to merge a different name in each label? I went to the following websites: http://www.kvalheim.org http://www.users.bigpond.com/auslandline/publisher/ and...

HOW TO EXTRACT (or lookup) DATA FROM A PIVOT TABLE
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C7032B.0406A590 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! Sheet 1 contains the raw data. Sales and cost of sales for customers.=20 Sheet 2 contains the pivot table that summarizes the data in sheet 1.=20 In Sheet 3(a further summary of the Pivot Table), I would like to put in = the customer name and extract the related sales and cost of sales = numbers from the pivot table. How can I extract the desired information? Regards SJ ------=_NextPart_000_000...

User login not deleting in GP8.0
Hi have created a new user ID & given access to a company. After loggin into GP8.0 and log-off from the company, am unable to delete this above created user . A message is displayed saying "Cannot drop login as the user is currently logged in. Am using the 'sa' as the user when deleting. This is not true as user activity table is clear, other than the final user sa. From the backend, on delete of user from GP (Tools>System>User), only user ID is deled from each of the Databases, but the SQL login remains in the security folder. Hi Un check the SQL options in ...

Delete someone elses reoccurring meeting from my calendar
a co-worker has left and I'm stuck with his weekly reoccurring meeting on my calendar. Yes, I can go week by week and delete it but can anyway help me delete them all - they go though full year 2010! Thanks! Switch to the all appointments or by category view to delete it. If you have multiple appointments, select the first, hold control and select the last. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlo...

Pivot Table accessing data from OLAP cube
Hi, I am creating a Pivot table using OLAP cube (in Excel 2007) on Windows Server 2003 OS and same Pivot Table using OLAP on Windows XP. The wierd problem i am facing is that on Windows Server 2003 OS, the moment I finish importing the cube, it shows me a screen like "Drop Row/Column Fields here" and on Windows XP, it simply shows the structure of Pivot table. Secondly,on functional side, if I email this spreadsheet across (created on Windows XP), then the other person is not able to edit/modify it, getting a warning message "Initialization of the Data Source faile...

How do I enter new data alphabetically into a workbook w/o scroll.
I have a list of information and periodically I have to add information into this list. The easiest way I found so far to do this is by scrolling through the list of information I have and finding the person I need to add to and inserting a new line. Is there a form that can be easily built that inserts information into its spot alphabetically? Hi Jillian:- Please check out:- http://www.pierrefondes.com/ - item number 42 (done in EXCEL 2007). This won't add it alphabetically but it does present you with an easy way to get data into a Workbook. If there is an...

Append data from external database
I need to import data from a live table in one database to a table in another database. The data I am importing is not indexed. I would like to be able to import this data into my table that is indexed. My table is indenticle structure except for the indexes. I only need to refresh my table intermittently, so I delete all the records from my table and append all the records including the most current records from the live table to my table. I am using a link to the live table. When I attempted the append I received an error "invalid argument." Is there a way to handle this w...

Data files
What is the difference between the **.mbf and **.mny files? In microsoft.public.money, duhmel wrote: >What is the difference between the **.mbf and **.mny files? > *.mbf is Money Backup File. You cannot do salv.exe or standard repair directly to a *.mbf file. ...

Outlook profile won't delete
When I click on a profile at start-up it says its not valid. In mail -email acounts it won't let me choose email accounts and when I pick profiles I get an error message: internal MAPI error -profile can not be found, contact administrator. I'm using XP and I've just installed Office 2007. Really having problems setting up profiles -HELP! CDNuser, you wrote on Tue, 21 Apr 2009 07:21:02 -0700: > When I click on a profile at start-up it says its not valid. > In mail -email acounts it won't let me choose email accounts and when I pick > profiles I get an error mes...

Find duplicate values in an array
Hi everyone, 21:00 on a Saturday night and I'm struggling with this! I have textual data in cells C158:L177 Unfortunately these are not all unique values ... I want to get the address of each dearched instance. In cell C179 I want to enter a search text which exists in the data. In D179 I want the address of the first instance, E179 the address of the second etc. For unique values the formula: =ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUCT(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4) works like a dream but for multiple instances, i...