1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Macro to hightlight dup rows.

Discussion in 'Business Applications' started by Dreambringer, Jan 5, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Ok here is what I have.

    I am working on a report that would identify duplicate rows on a spreadsheet and hightlight them.

    As of right now I am useing:
    Code:
    =IF(AND(E3=E2,Q3=Q2),"Repeat","Unique"))
    
    Then I highlight all the "Repeat" rows and resort them.

    I was I have tried to come up with a macro and do not have much other then how to get the cells to highlight

    Code:
        With Selection.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
    
    I do know I am going to have to use an IF statement in the VB but not sure what comes next.

    Thanx in advance.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi DB. I'm sure Zack covered this recently ... if you want to do nothing more than highlight them, why not skip the VBA and stick with a Conditional Formatting formula?

    I would only expand on what you already have very slightly to catch blank rows:

    =AND(E2=E1,Q2=Q1,E1<>"")

    (the above from row 2 down, assume row 1 = header)
     
  3. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    The only reason I was looking to do it in VB was because it will be use by more then 1 person, and on more then 1 report, so I was trying to come up with a way I can carry it on my personal.macros.

    I thought I remember seeing it (Zacks Post), but did a search and could not find it. I try not to ask dup questions, so usally search first.

    Thanx for the advice.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hey there,

    For duplicates, I use brettdj's Duplicate Master ...

    http://members.iinet.net.au/~brettdj/

    There is a software for fuzzy duplicates, but you must pay for it. I've heard good things about it ...

    http://www.ablebits.com/excel-duplicates-find-remove-addins/

    If these don't work for you we can come up with a custom VBA solution for you. I, too, like Conditional Formatting as a solution for duplicates as well. It's fast, it's native and easy to apply. This can also be done via VBA.

    The good part about using CF is that it will work (if setup correctly) more than just a one time solution but through all changes.
     
  5. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    No its not nessisary to come up with a custom VBA if you think that CF is way to go. I was just looking for something that I did not have to set up on every sheet that I wanted to do this on.

    Thanx for the advice and I will check the links.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'm a little hesitant to go either way with a suggestion as I don't think I fully understand all the implications with this. I would not say one solution is better than the other at this point, but depending on your circumstances that may change.
     
  7. Dreambringer

    Dreambringer Thread Starter

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Well here is what we have and then you can decide.

    I pull a report from 2 differnt systems then combine the list.

    How I determine dups are by 2 fields.

    E= Client Name
    Q=Volume Produced

    Once I combine the list, and sort them by Client Name, is when I use the IF(AND from above.

    If the two lines are dups then I would like to flag one of them with a highlight of somesort.

    Am I making any sence?
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Okay, so I posted, message said server was too busy and I lost my post. Grrr! So let me recap a shorter version of what I had.

    I would use Data | AdvancedFilter | Unique values | copy to new location.

    If the entire process in Excel took longer than 3-5 minutes, I'd create a routine to automate this - if it were me.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/431346

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice