PDA

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