Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding duplicates and deleting based on another column
I have a spreadsheet of data that I have compiled from 6 different workbooks.
I have used a true false statement to identify the duplicated and now I need to delete them based on which spreadsheet they came from. The consolidated spreadsheet I have currently has 28,000 records, I am currently deletleting them manually but this will take me until I am 100 to go thru. Any help on this would be great. In column A I have numbers: 56088769 57499354 60175071 60175071 60175071 5608437X 5608437X 5608437X As you can see there could be 2 to 6 duplicate numbers. I need to find the duplicates in column A, then evaluted column J to see where the source of the data came from. The choices a Raw 02-06 PG &E Composite Data PG&E Data 08 This is also the order of choice, if there are 4 duplicates and Raw 02-06 is an option then delete the rest of the duplicates leaving only this one. If there is a duplicate and raw isn't available then pick option 2 and so on. Thank you for your help, it is greatly appreciated. Tim Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding duplicates and deleting based on another column
tpeter;536940 Wrote: I have a spreadsheet of data that I have compiled from 6 different workbooks. I have used a true false statement to identify the duplicated and now I need to delete them based on which spreadsheet they came from. The consolidated spreadsheet I have currently has 28,000 records, I am currently deletleting them manually but this will take me until I am 100 to go thru. Any help on this would be great. In column A I have numbers: 56088769 57499354 60175071 60175071 60175071 5608437X 5608437X 5608437X As you can see there could be 2 to 6 duplicate numbers. I need to find the duplicates in column A, then evaluted column J to see where the source of the data came from. The choices a Raw 02-06 PG &E Composite Data PG&E Data 08 This is also the order of choice, if there are 4 duplicates and Raw 02-06 is an option then delete the rest of the duplicates leaving only this one. If there is a duplicate and raw isn't available then pick option 2 and so on. Thank you for your help, it is greatly appreciated. Tim Peter If this is a one-off exercise, then beacuse the data source names don't sort naturally into your order of preference I would do find and replace 3 times on column J to put a numeral in front to get: 1Raw 02-06 2PG &E Composite Data 3PG&E Data 08 (you'll reverse that later) then sort your consolidated sheet primarily sorting on column A, but secondarily on column B ascending. Now for each block of duplicates, the 1Raw 02-06 ones(s) should be at the top. Now it's just a case of running this macro, which deletes all the lower duplicates, after selecting the entire block rows-wise, but only column A: Sub blah() toprow = Selection.Row bottomrow = Selection.Rows.Count + toprow - 1 For i = bottomrow To toprow + 1 Step -1 If Cells(i, "A").Value = Cells(i - 1, "A").Value Then Rows(i).Delete Next i End Sub Now do a find and replace (well 3 actually) on column J to restore the original data source names. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147576 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding duplicates and deleting based on another column
I have renamed the 3 criteria and sorted in assending order by column A and
secondary assending by column B. When I run the macro it says variable not defined. a b c d e f 25123670 AC250 LIVERMORE 0.85 4/27/2004 25151368 AC250 BENICIA 0.62 6/7/2004 25168891 AC250 SAN FRANCISCO 0.05 12/23/2002 g h i j k 8/17/2004 American Test 5-20-07 FALSE 3/29/2007 9/29/2004 American Test 5-20-07 FALSE 8/6/2003 American Test 5-20-07 FALSE "tpeter" wrote: I have a spreadsheet of data that I have compiled from 6 different workbooks. I have used a true false statement to identify the duplicated and now I need to delete them based on which spreadsheet they came from. The consolidated spreadsheet I have currently has 28,000 records, I am currently deletleting them manually but this will take me until I am 100 to go thru. Any help on this would be great. In column A I have numbers: 56088769 57499354 60175071 60175071 60175071 5608437X 5608437X 5608437X As you can see there could be 2 to 6 duplicate numbers. I need to find the duplicates in column A, then evaluted column J to see where the source of the data came from. The choices a Raw 02-06 PG &E Composite Data PG&E Data 08 This is also the order of choice, if there are 4 duplicates and Raw 02-06 is an option then delete the rest of the duplicates leaving only this one. If there is a duplicate and raw isn't available then pick option 2 and so on. Thank you for your help, it is greatly appreciated. Tim Peter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding duplicates and deleting based on another column
I have added numbers to the other 3 options and sorted it by column A and J
but still get varible is not defined on the macro stoping as soon as it starts. "tpeter" wrote: I have renamed the 3 criteria and sorted in assending order by column A and secondary assending by column B. When I run the macro it says variable not defined. a b c d e f 25123670 AC250 LIVERMORE 0.85 4/27/2004 25151368 AC250 BENICIA 0.62 6/7/2004 25168891 AC250 SAN FRANCISCO 0.05 12/23/2002 g h i j k 8/17/2004 American Test 5-20-07 FALSE 3/29/2007 9/29/2004 American Test 5-20-07 FALSE 8/6/2003 American Test 5-20-07 FALSE "tpeter" wrote: I have a spreadsheet of data that I have compiled from 6 different workbooks. I have used a true false statement to identify the duplicated and now I need to delete them based on which spreadsheet they came from. The consolidated spreadsheet I have currently has 28,000 records, I am currently deletleting them manually but this will take me until I am 100 to go thru. Any help on this would be great. In column A I have numbers: 56088769 57499354 60175071 60175071 60175071 5608437X 5608437X 5608437X As you can see there could be 2 to 6 duplicate numbers. I need to find the duplicates in column A, then evaluted column J to see where the source of the data came from. The choices a Raw 02-06 PG &E Composite Data PG&E Data 08 This is also the order of choice, if there are 4 duplicates and Raw 02-06 is an option then delete the rest of the duplicates leaving only this one. If there is a duplicate and raw isn't available then pick option 2 and so on. Thank you for your help, it is greatly appreciated. Tim Peter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding duplicates and deleting based on another column
EITHER[/b] REMOVE *OPTION EXPLICIT from the top of the module where you have the macro [b]or* add the following line at the top of the macro: Dim Toprow, bottomrow, i -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147576 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding duplicates and deleting based on another column
Thanks for all your help. I sorted by column A and J. I was not seleting
everything I wanted sorted so it was breaking. Went from 23,650 records to 12,303 when I ran by check for duplicates =countif(a:a,a1)1 everything came back false. Thanks again for all of your help it saved me a lot of sanity. Another interesting note is I brought this file home to work on and I have 2007 and there is a remove duplicates button that gave the exact same results. Tim Peter "p45cal" wrote: EITHER[/b] REMOVE *OPTION EXPLICIT from the top of the module where you have the macro [b]or* add the following line at the top of the macro: Dim Toprow, bottomrow, i -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147576 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting duplicates within a column? | Excel Discussion (Misc queries) | |||
Finding duplicates based on 2 columns | Excel Worksheet Functions | |||
Finding duplicates in a column | Excel Programming | |||
Finding/deleting duplicates and merging cells | Excel Worksheet Functions | |||
Finding and Deleting duplicates in a column | Excel Worksheet Functions |