-
Jan 29th, 2015, 03:26 PM
#1
Thread Starter
PowerPoster
delete duplicate row
In excel sheet have:
col A col B col C
AAA 1 0
BBB 1 0
AAA 1 0
AAA 1 0
... ecc
possible to check the 3 colum and delete duplicates rows, with msgbox alert, similar:
Attention you have 2 duplicate row for :AAA 1 0, can you maintain only the unique row?
if user choice yes, delete the two lines, and maintain the only unique row...
new scenario after yes from user
AAA 1 0
BBB 1 0
-
Jan 30th, 2015, 04:55 AM
#2
Re: delete duplicate row
What about other duplicate rows later on?
Like
col A col B col C
AAA 1 0
BBB 1 0
AAA 1 0
AAA 1 0
BBB 1 0
CCC 1 0
AAA 1 0
BBB 1 0
CCC 1 0
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 2nd, 2015, 03:59 PM
#3
Frenzied Member
Re: delete duplicate row
there is a way of identifying the duplicates that allows you to markup your sheets and then simplify them.. it works no matter how distant the duplicates are.
the psudocode is as follows
start macro
add/use identification column "IC"
set "IC" values to 1 where data exists on row
start at "IC" row "1" - first appropriate data row
walk the rows until you reach "ICrow"=nul you have not put 1 here
during walk if the current row "IC" =2 step forward do not start the comparisons
if "IC"=1 compare the row excluding "IC" with the following rows until "ICrow"=nul
if the rows are equal mark "ICrow"=2 this is a duplicate
having completed the walk through the data laden rows
method 1
delete every row whoes "IC" is 2
close up the rows
method 2
sort data laden rows by the "IC" value
the "IC"=1 rise to the top and so deletion of "IC"=2 results in a contiguos table of unique rows
job done
end macro
-
Feb 3rd, 2015, 01:21 AM
#4
Fanatic Member
Re: delete duplicate row
You could just use the Range.RemoveDuplicates method
to identify do something like this
copy sheet
remove duplicates on copied sheet
compare sheets
if they are not the same there must have been duplicates removed(maybe put the ranges into arrays and compare bits)
add extra code if you want to point out individual records
if they want to remove the records then delete first sheet replace with new sheet
no looping needed
Yes!!!
Working from home is so much better than working in an office...
Nothing can beat the combined stress of getting your work done on time whilst
1. one toddler keeps pressing your AVR's power button
2. one baby keeps crying for milk
3. one child keeps running in and out of the house screaming and shouting
4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
5. working at 1 O'clock in the morning because nobody is awake at that time
6. being grossly underpaid for all your hard work
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|