Duplication removal with condition...please help, I don't have much time left...

[Mel Shad]

Hello all

I have a sheet with more than 14K lines and 25 columns of data.
In column A I have case numbers which is NOT unique.
Some of the case numbers in column A have a blue background color (Fill Color) and some have no background color.

I have sorted the entire sheet after the case numbers in column A and have the biggest numbers on top.
Now I have lots of lines with the same case numbers repeated after each other where some lines have blue fill color and some have white background color (no fill color).

I would love to compare the lines one by one and find the duplicate case numbers. Once a duplicate case number is found and IF the fill color is white, empty the cell or remove the row. And if the duplicates fill color is blue, do nothing and go to the next cell.

Hope you gods out there can help me out. Thanks a ton for any help.
I’m running out of time, please help.
Thanks again

[greggpetersen75]

Use a header andcreate a column next to a insert an if statement = In B2 write, =IF(A2=A3,"True",If(B1="True",If(A1=A2,"True","False")). Then bring the formula to the bottom of the range. All the Trues will be dups.

[greggpetersen75]

Please Login or Register  to view this content.

Sorry this is the formula. I was sleeping at the wheel

[greggpetersen75]

Or if you are really into it you could do a pivot table and Pivot on the Case numbers and use the count case numbers in the pivot table. Copy and paste special values on the pivot table. Remove any rows with a count of 1 and then you would know how many duplicate Cases you had. If you wanted to examine them individually then you could do a vlookup back to the worksheet with all of the data. Any result that was an #N/A was not a dup.

[Mel Shad]

thanks for the code, it was very nice of you. however your code doesn't look at the fill color. how can i get the rows with no fill color deleted.

thanks again for all your help.

[greggpetersen75]

Hi, if you are just interested in removing duplicates why don't you use the remove duplicates function on the data ribbon.

[greggpetersen75]

Or you could use an auto-filter on the color blue use ctrl+shift+ down arrow and select delete entire row from the ribbon.