Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
Is it possible to usea macro to look at every row on a sheet and if an
row matches another on the same sheet (for example if row 3 is matche in row 717) the second row is automatically deleted and copied to a ne worksheet - to allow review of duplicates at a later time. It is possible that there will be more than one duplicate, ie row 3 i matched by row 717 and row 999 and both of these duplicates should b carried to the new sheet. Thanks for any hel -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
Easier might be to copy the uniques to a new sheet (see the Advanced filter
under Data=filter) Also, what do you consider to be a duplicate - an index cell (single column) or do all values in the row need to be matched. As a start you might look at Chip Pearson's page on uniques and duplicates. http://www.cpearson.com/excel/duplicat.htm -- Regards, Tom Ogilvy ali wrote in message ... Is it possible to usea macro to look at every row on a sheet and if any row matches another on the same sheet (for example if row 3 is matched in row 717) the second row is automatically deleted and copied to a new worksheet - to allow review of duplicates at a later time. It is possible that there will be more than one duplicate, ie row 3 is matched by row 717 and row 999 and both of these duplicates should be carried to the new sheet. Thanks for any help --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
ali
No need for a macro. Insert a new worksheet. Select your data range on original sheet. DataFilterAdvanced Filter. Check "Unique records only" and "copy to a new location". Your "listrange" will be already entered. In the "copy to" click on the Collapse Dialog button and select your new worksheet A1. OK your way out. You can then delete those same records from the original sheet, leaving only the duplicates for later review. Filtering Tutorial at Debra Dalgleish's site....... http://www.contextures.on.ca/tiptech.html More on Duplicates marking, preventing at Chip Pearson's site.... http://www.cpearson.com/excel/topic.htm Scroll down to "D" section and select Duplicate topics. Gord Dibben Excel MVP On Sun, 14 Dec 2003 13:52:34 -0600, ali wrote: Is it possible to usea macro to look at every row on a sheet and if any row matches another on the same sheet (for example if row 3 is matched in row 717) the second row is automatically deleted and copied to a new worksheet - to allow review of duplicates at a later time. It is possible that there will be more than one duplicate, ie row 3 is matched by row 717 and row 999 and both of these duplicates should be carried to the new sheet. Thanks for any help --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
Thanks for that but i'm trying to create something where an
inexperienced excel user could simply click a button and it would automatically occur. I admit this is probably extremely difficult but i believe someone out there has the knowledge to help me!! (Nb. I am interested in copying all of the data, ie every column, that exists in the duplicated rows) Thanks very much --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
No one can help you if you won't answer the questions.
-- Regards, Tom Ogilvy ali wrote in message ... Thanks for that but i'm trying to create something where an inexperienced excel user could simply click a button and it would automatically occur. I admit this is probably extremely difficult but i believe someone out there has the knowledge to help me!! (Nb. I am interested in copying all of the data, ie every column, that exists in the duplicated rows) Thanks very much --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
ali
That's why Excel has the Macro Recorder. Follow the steps outlined under "Extract Data to Another Sheet" on Debra's page while using the Recorder. My steps in the earlier post will not work because I started on the wrong sheet so ignore that befuddled advice. Gord On Sun, 14 Dec 2003 15:14:47 -0600, ali wrote: Thanks for that but i'm trying to create something where an inexperienced excel user could simply click a button and it would automatically occur. I admit this is probably extremely difficult but i believe someone out there has the knowledge to help me!! (Nb. I am interested in copying all of the data, ie every column, that exists in the duplicated rows) Thanks very much --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
Tom, apologies for missing your question. In response, i am only
interested if the whole row matches another. In response to the suggestion to use macro recorder and advanced filter... when i tried it the advanced filter part was not recorded. Is this normal? Thanks for all the help --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
ali
Recorded macro..... Sub Macro1() ' ' Macro1 Macro ' Macro recorded 12/14/2003 by Gord Dibben Sheets.Add ''inserts a sheet and copies unique rows to A1 on new sheet Sheets("Day").Range("C1:H500").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("A1"), Unique:=True End Sub Gord Dibben Excel MVP On Sun, 14 Dec 2003 16:21:04 -0600, ali wrote: Tom, apologies for missing your question. In response, i am only interested if the whole row matches another. In response to the suggestion to use macro recorder and advanced filter... when i tried it the advanced filter part was not recorded. Is this normal? Thanks for all the help --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
Yes. You can establish a criteria range and put in conditions like equal to
, less than, greater than using the operators Header1 ="<=21" for example. for multiple columns, being on the Same row is an AND relation and in sequential rows is an OR relation. You can have a computed condition as well. You use a dummy column name for this (shouldn't match any column name in the data and the formula refers to the first row of data. Excel help is fairly good for explaining the possibilities and has examples. -- Regards, Tom Ogilvy "Dave" wrote in message u... Hi Gord, Nice, I didn't realise that Advanced Filter could handle whole rows for unique records..... Do you know if it is possible to use criteria with Advanced Filter in VBA? When I've used Advanced Filter with VBA I've always inserted a blank column, added the criteria criteria, done the filtering then removed the inserted column. Thanks Dave <Gord Dibben wrote in message ... ali Recorded macro..... Sub Macro1() ' ' Macro1 Macro ' Macro recorded 12/14/2003 by Gord Dibben Sheets.Add ''inserts a sheet and copies unique rows to A1 on new sheet Sheets("Day").Range("C1:H500").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("A1"), Unique:=True End Sub Gord Dibben Excel MVP On Sun, 14 Dec 2003 16:21:04 -0600, ali wrote: Tom, apologies for missing your question. In response, i am only interested if the whole row matches another. In response to the suggestion to use macro recorder and advanced filter... when i tried it the advanced filter part was not recorded. Is this normal? Thanks for all the help --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
Hi Gord,
Nice, I didn't realise that Advanced Filter could handle whole rows for unique records..... Do you know if it is possible to use criteria with Advanced Filter in VBA? When I've used Advanced Filter with VBA I've always inserted a blank column, added the criteria criteria, done the filtering then removed the inserted column. Thanks Dave <Gord Dibben wrote in message ... ali Recorded macro..... Sub Macro1() ' ' Macro1 Macro ' Macro recorded 12/14/2003 by Gord Dibben Sheets.Add ''inserts a sheet and copies unique rows to A1 on new sheet Sheets("Day").Range("C1:H500").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("A1"), Unique:=True End Sub Gord Dibben Excel MVP On Sun, 14 Dec 2003 16:21:04 -0600, ali wrote: Tom, apologies for missing your question. In response, i am only interested if the whole row matches another. In response to the suggestion to use macro recorder and advanced filter... when i tried it the advanced filter part was not recorded. Is this normal? Thanks for all the help --- Message posted from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify and move duplicate rows?
First Last Address City State Zip Phone (various other information
columns i dont want to be part of the dup data criteria) i want to delete rows that have the same first, last and phone regardless of what is in the other columns i tried using Sub DeleteTheOldies() Dim RowNdx As Long For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1 If Cells(RowNdx, "A").Value = Cells(RowNdx - 1, "A").Value Then If Cells(RowNdx, "B").Value <= Cells(RowNdx - 1, "B").Value Then Rows(RowNdx).Delete Else Rows(RowNdx - 1).Delete End If End If Next RowNdx End Sub but that will delete entries that have differnt phone numbers. some people in my list have same first and last names but differnt phone numbers because they are unique ppl can you help me get rid od my dup entries tried that advanced sort but it left dup entries because of various other information columns i dont want to be part of the dup data criteria some would be PO box abd others P.O. box and be left in caude of formating of that data thank you, Grape --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to identify duplicate rows & staffs xfr frm one depart to anot | Excel Discussion (Misc queries) | |||
Identify duplicate rows | Excel Discussion (Misc queries) | |||
Need to identify duplicate entries in a Table | Excel Worksheet Functions | |||
How do I identify duplicate numbers in Excel? | Excel Discussion (Misc queries) | |||
identify duplicate enteries | Excel Worksheet Functions |