Remove or Highlight Duplicates only if all lines the same, if not change font color

Hi everyone,

I have a sheet with items, sizes, and sale prices columns. I want to remove all the duplicate lines, only if all lines 3 columns are the same. I may have 2 lines of one item, but 10 of another. Also I may have one size different than the others or sale price different, so I want to keep all lines. I tried using remove duplicates, but I want to keep all lines if there is any difference.

So in the example, I want to keep all sugar lines and change font color to blue and just keep the first oil line because all columns are the same. Thank you.

Column A Column B Column C
Sugar 16oz 1.00
Sugar 4 lbs 4.00
Sugar 16oz 1.00

Oil 64oz. 2.00
Oil 64oz. 2.00
Oil 64oz. 2.00
Oil 64oz. 2.00

you mean you wanna delete rows if 3 columns has the same values on its cells?
I am not sure if get what you mean, or better, can you please post a sample workbook, on 1st sheet is your current data, and 2nd sheet is the data desire after the macro is run...

oh wait! I think I got what you mean, and I think your problem can be solved through formulas alone.
you can use the "Countifs" function

insert this formula on any column: (and name it "Duplicates" column)

Please Login or Register  to view this content.

and you can just drag it down until the bottom of the data..
then you can sort it by the Duplicates column from largest to smallest, so if the count ifs function returns a number greater than 1, then it means there's a duplicate, then you can delete all of them at once...

that's a simple solution...

(To the moderators, i'm not sure if its ok to use codetags for formulas.. sory in advance if its not ok)

if this helped, click the star "*" icon below that says "Add reputation"

Try this out:

Duplicate Delete.xlsm



specifics:
it can work on any worksheet in the same workbook
macro's has to be enabled for it to work
it starts at cell A1
it stops when the next 3 consecutive cells are blank in column A
it uses the countifs function, that in case the row has the same match, it will delete it, if not, then it will go to the next row below it.

to activate the macro, you can use the shortcut: alt + w + m + v
you'll see a macro list, the macro is named "a" so just click "Run"

**Note, after the macro is executed, changes cannot be undone so always save a copy first



if this helped, click the star "*" icon below that says "Add reputation"

woops.. sorry I misunderstood your goal,
this one is modified:

Duplicate Delete.xlsm

it will not remove any rows, but will only highlight the rows with a match

Originally Posted by K15

I have a sheet with items, sizes, and sale prices columns. I want to remove all the duplicate lines, only if all lines 3 columns are the same. I may have 2 lines of one item, but 10 of another. Also I may have one size different than the others or sale price different, so I want to keep all lines. I tried using remove duplicates, but I want to keep all lines if there is any difference.

the statements in bold confuses me...
the first macro I created "a" does the first objective - remove the duplicates...
the second macro modified "a" does the second objective - only highlight in blue the duplicates and keep all of them.

so, which is it?

Ok, sorry for the confusion. I tried to clarify it in post number #8, but I'll try again. I have a list of items going on sale. The headers are Description, Size, and Sale Price. Let's say I have sugar going on sale. In this example below, I have three lines of sugar, but because they all not the same, size and price, I want to keep all 3 lines, 16oz., 16oz., and 4lbs. With the countif statement, I would lose one of sugar, 1-4lbs, 16oz., $0.99. If all lines are the same, all 16oz. and $0.99, then I would like to delete the duplicates.

Keep
Description Size Sale Price
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 4lbs. 2.99

Delete Dups
Description Size Sale Price
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 16oz. .99

ahh.. if you mean retain all rows that are unique, then i guess its already solved..

my post #6 attachment has it, you can use the macro i created "a"
short cut: alt + w + m + v
then click run

it will eliminate the duplicates and retain the unique rows.
So with these:

Description Size Sale Price
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 4lbs. 2.99


it will retain these:
Description Size Sale Price
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 4lbs. 2.99



now these two below on your post appear the same to me, why would you wanna keep both?

Keep
Description Size Sale Price
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 16oz. .99
Sugar, 1-4lbs. 4lbs. 2.99

give a try the macro i created
and if it worked,

click my star "*"

Originally Posted by bhausib23

I have a sheet with items, sizes, and sale prices columns. I want to remove all the duplicate lines, only if all lines 3 columns are the same. I may have 2 lines of one item, but 10 of another. Also I may have one size different than the others or sale price different, so I want to keep all lines. I tried using remove duplicates, but I want to keep all lines if there is any difference.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread