Excel - Macro to hightlight dup rows.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
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)
 

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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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.
 
Joined
Jul 25, 2004
Messages
5,458
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.
 

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?
 
Joined
Jul 25, 2004
Messages
5,458
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top