+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

    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
    Last edited by K15; 08-28-2013 at 05:04 PM.

  2. #2
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

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

    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...
    don't worry, there's a lot of people that are far more confused than you
    but if you liked what i suggested. Click for me the "Add Reputation" - that way, we'd be both happy.

  3. #3
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

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

    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"

  4. #4
    Registered User
    Join Date
    10-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

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

    Yes, that is correct. I want to delete only if all three columns are the same for all lines. Attached is a sample.

    Thank you
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

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

    Hey Ag273n, Thanks. I tried your suggestion, but it doesn't quite work. It is like running the delete duplicates in Excel, so I'll have the unique ones with a value of 1 and the rest with a value greater than one. I want to be sure to keep all the lines, if any lines are different. I am not sure if adding all lines to an array and then comparing it would work.

  6. #6
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

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

    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"

  7. #7
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

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

    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

  8. #8
    Registered User
    Join Date
    10-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

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

    The countif method doesn't work because I want to save all lines regardless if there are duplicates, if any of the three columns are different. In the sample workbook, I kept all lines for Bounty, Clos Du Bois, and Herbal Essences because there was different sizes or prices. WHile, All, Ajax, and Bacardi had all three columns the same, so I am able to delete the duplicate lines.

    Thank you for your help, ag273n

  9. #9
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

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

    Quote Originally Posted by K15 View Post
    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?

  10. #10
    Registered User
    Join Date
    10-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

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

    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
    Last edited by K15; 08-28-2013 at 08:57 PM. Reason: formatting

  11. #11
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

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

    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 "*"
    Last edited by ag273n; 08-29-2013 at 06:45 PM. Reason: added detail

  12. #12
    Registered User
    Join Date
    10-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

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

    I actually want to keep all rows, unique and duplicates, if there is any difference. It's actually a discount column instead of a size column, so each item may have a different discount depending on the vendor. I just used size because it should be the same idea. So the only thing i could think of is macro that will compare the block of lines, compare, and if all the same, delete duplicates, and if any are different go to the next block. I really appreciate all your help.

  13. #13
    Registered User
    Join Date
    10-05-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    7

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

    Hi ag273n, I figured out a solution using your original formula. I use the original formula in one column, compare the just the description in the next column, and then compare if equal. I can then delete the true and keep the false. It's a few extra steps, but it works and saves time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to highlight cell (or change font color) when condition is met
    By Maezee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2013, 04:17 PM
  2. Replies: 2
    Last Post: 02-04-2013, 02:00 PM
  3. Change font color based on font color of another cell
    By Ibyers in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2012, 09:36 AM
  4. change font color and font style in shape
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-05-2011, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1