Remove row duplicates depending on values from other rows

**soli004** · 02-21-2011, 09:41 AM

Hello!
This is my problem!
I have duplicates in Column A (RowID) that has to be removed, but I need to save the correct row that has the same value in previous row in Column F (Link). If there is no same value in Column F (can happen) it has to save the row with the lowest value in Column E (Type).

I attache the sample file where I have colored the duplicates in red and at the bottom the result as the file should look like. I hope it is understandable...

This is too complex for me to solve and the files can have up to 10 000 rows so not a very fun doing manually!

Regards

**blane245** · 02-21-2011, 11:56 AM

What happened to rowID 171? Looks like row 182 should have been kept according to your Column E rule.

**soli004** · 02-21-2011, 01:40 PM

Originally Posted by blane245

What happened to rowID 171? Looks like row 182 should have been kept according to your Column E rule.

OPPS!! Sorry that was a miss by me when doing it manually...
You see!!! If it could be done by code it is much safer

Thanks

Made an new update on the file...

**blane245** · 02-21-2011, 03:36 PM

try out this macro

Please Login or Register  to view this content.

**soli004** · 02-22-2011, 04:06 AM

Originally Posted by blane245

try out this macro

Thanks!

I see another problem that showed up! Column F can also have "0" as values and it seems to turn things wrong...
Have a look at row 3 and 4, (attached new file) when running the macro it saves the wrong value (saves the one with the highest value instead of lowest in Column E). It saves row 4 instead of row 3 that has the lower value of "12" (row 4 "15" in Column E)

I see also on row 13, 14, 15 that it save the one with the highest value and not the lowest in Column E both row 13 and 14 has value "6" and row 15 has "12" and it saves row 15 wich is wrong. I think it looks for the Column F value and if "match" then ignore the values in Column E

I did miss that explanation I can see now... sorry
Column E is the important value and has to be the lowest of all duplicate and I see the problems turning up now of using Column F for the "match" as in the above example it picked the highest value (row 13-15) because of the match in Column F (my bad explanation) I guess it is better to go for the lowest value in Column E for the duplicates rows and be happy with it and forget about values in Column F

Thanks for the help and sorry for my bad explanation...

**blane245** · 02-22-2011, 08:28 AM

here's another version. Note that i included a test for the presence if 0 in the Link. If the previous link value is 0, then you will never get a link match and the smallest type will be selected.

Are there any other special cases?

Please Login or Register  to view this content.

**soli004** · 02-22-2011, 06:09 PM

Originally Posted by blane245

here's another version. Note that i included a test for the presence if 0 in the Link. If the previous link value is 0, then you will never get a link match and the smallest type will be selected.

Are there any other special cases?

Thanks!
I will check and get back, I am on the road for a few days. Really appricate the great work!