Michel,
Hope I am not too late,
here is the revised macro which will highlight the row if
any municipality have two or more mayors in the same year
Regards,
Cecil
Dim LRowMcity As Double
Dim i As Double
Dim x As Double
Dim y As Double
Dim McityRng As Range
Dim YearRng As Range
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:C" & LRow).Interior.ColorIndex = -4142
Range("E1:F1").EntireColumn.Clear
Range("A1:A" & LRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("E1"), Unique:=True
Range("B1:B" & LRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("F2"), Unique:=True
LRowMcity = Range("E" & Rows.Count).End(xlUp).Row
LRowYear = Range("F" & Rows.Count).End(xlUp).Row
Range("F2:F" & LRowYear).Sort Key1:=Range("F3"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("F3:F" & LRowYear).Copy
With Range("F1")
..PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False
Range(Cells(2, 6), Cells(LRowYear, LRowYear + 4)).Clear
Set McityRng = Range("E1:E" & LRowMcity)
Set YearRng = Range(Cells(1, 5), Cells(1, LRowYear + 3))
For i = 2 To LRow
x = Evaluate("Match(" & Range("A" & i).Address & "," & _
McityRng.Address & ", 0)")
y = Evaluate("Match(" & Range("B" & i).Address & "," & _
YearRng.Address & ", 0)") + 4
If IsEmpty(Cells(x, y)) Then
Cells(x, y).Value = Range("C" & i).Value
Else
Range("A" & i & ":C" & i).Interior.ColorIndex = 3
End If
Next i
End Sub
"Michel Rousseau" wrote in message
...
Cecil, thank you for the macro. I will try it out.
In case an error has been made and someone has put the names of two
mayors for the same municipality and year, is there a way to add
something to the macro so that it flags this. Maybe, it could add
additional lines for the country and put at the country and year
intersections all the names found. It would be good also if the names
of the mayors were turned red when there is more than one for a country
and year.
It would be really good if this could be put in the macro also. Once
again, thank you.
Michel
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!