View Full Version : Macro to sort Ascending,identify duplicates& highlight yellow
Doodle80
03-16-2009, 02:28 PM
HI,
Trying to build a macro- to sort the list(ascending) of numbers in column A and identify duplicates and highlight them yellow. Only catch to this is one month the list might have 100 cells information, next month it only be 50.
I am guessing it will incorporate if then else statement(for identifying duplicates) and relative range to do both sorting.
Any suggestions is greatly appreciated.
hardlife
03-16-2009, 02:53 PM
Hi, You can try this, firstly mark column A = select, then set Format, Conditional formating, use formula, set custom format.
formula:
=COUNTIF(A:A,A1)>1
=COUNTIF(A:A;A1)>1 country oriented, works in Czech Republic
http://www.mrexcel.com/articles/duplicates-with-conditional-formatting.php
GOOD LUCK AND GOOD NIGHT
Pavel Humenuk
hardlife
03-16-2009, 02:59 PM
Sub SortAndMarkDuplicatesInColumnA()
Columns("A:A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("A:A").Select
'Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(A:A,A1)>1" 'for our country Czech Republic use "=COUNTIF(A:A;A1)>1"
Selection.FormatConditions(1).Interior.ColorIndex = 27
End Sub
GOOD LUCK AND GOOD NIGHT
Pavel Humenuk
Doodle80
03-16-2009, 03:38 PM
Thanks for helping-
1) Conditional formatting is not doing anything-Followed step by step procedure.
2) Tried Macro - IT SAYS - Compile Error:
Expected:Expression
Sorry little new @ this.
Thanks Again.
mdmackillop
03-16-2009, 04:29 PM
Try this version of Hardlife's code
Option Explicit
Sub SortAndMarkDuplicatesInColumnA()
Range("A1").Select
With Columns("A:A")
.Sort Key1:=Range("A2"), Order1:=xlAscending
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(A:A,A1)>1"
.FormatConditions(1).Interior.ColorIndex = 27
End With
End Sub
mdmackillop
03-16-2009, 04:31 PM
Pavel,
Problem I think is separator in "=COUNTIF(A:A;A1)>1". Comma instead of semi-colon.
Doodle80
03-17-2009, 08:28 AM
Thanks Guys it really worked!! here is another question what if i want to extended the sorting to column J as Column A Numbers have information extended till column J.
I am trying to learn about Range- eventually wanna sort column A to J and highlight the duplicates.
Thanks a lot..
Bob Phillips
03-17-2009, 08:50 AM
Option Explicit
Sub SortAndMarkDuplicatesInColumnA()
Range("A1").Select
With Columns("A:J")
.Sort Key1:=Range("A2"), Order1:=xlAscending
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($A:$A,$A1)>1"
.FormatConditions(1).Interior.ColorIndex = 27
End With
End Sub
hardlife
03-17-2009, 09:39 AM
It is beautiful to see clear code, Thanks a lot to Everybody,
It is also beautiful to see help pro users to us,
mdmackillop, xld and Doodle80, all other users,
wish You to have a nice day,
Pavel Humenuk
Doodle80
03-17-2009, 09:44 AM
Thanks everyone!!
mdmackillop
03-17-2009, 11:01 AM
Hi Bob,
Out of interest, can we set the Conditional Formatting without selecting A1 first? My attempts were unsuccessful.
Regards
Malcolm
hardlife
03-17-2009, 11:09 AM
=COUNTIF($A$2:$A$10,A2)>1
happy and sunny winter day to everybody
Pavel Humenuk
mdmackillop
03-17-2009, 11:19 AM
That gives me
=COUNTIF($A$2:$A$10,IT65535)>1
or using $A2 I get
=COUNTIF($A$2:$A$10,$A65535)>1
Bob Phillips
03-17-2009, 11:20 AM
Yes you can, but you have to adjust it according to what is selected.
For instance, suppose you want a formula of =A1>7 in the range A1:E10, this will work wherever you are
'just to prove it works
Range("M17").Select
With Range("A1:E10")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & ">7"
.FormatConditions(1).Interior.ColorIndex = 3
End With
Bob Phillips
03-17-2009, 11:21 AM
In the example in this thread, it would be
Sub SortAndMarkDuplicatesInColumnA()
Dim mpFormula As String
With Columns("A:J")
.Sort Key1:=Range("A2"), Order1:=xlAscending
.FormatConditions.Delete
mpFormula = "=COUNTIF($A:$A,$" & ActiveCell.Address(False, False) & ")>1"
.FormatConditions.Add Type:=xlExpression, Formula1:=mpFormula
.FormatConditions(1).Interior.ColorIndex = 27
End With
End Sub
mdmackillop
03-17-2009, 11:23 AM
Thanks Bob, I'll have a play with that method.
hardlife
03-17-2009, 11:29 AM
May be me is wrong, but it happened to me in office2007,
if me pasted formula, conditional formating was set wrongly,
so me edited it again and it was OK, if it was the question.
when it is not working select A2:A10 and
edit stored conditional formating again,
Pavel
=COUNTIF($A$2:$A$10,A2)>1
That gives me
=COUNTIF($A$2:$A$10,IT65535)>1
or using $A2 I get
=COUNTIF($A$2:$A$10,$A65535)>1
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.