formatting duplicate values

Peo Sjoblom

unread,

May 6, 2002, 11:39:23 AM5/6/02

to

You can use conditional formatting,

assume the values are in column A, select column A by clicking the column header,
do format>conditional formatting, select formula is and use

=COUNTIF($A$1:A1,A1)>1

click the format button, choose patterns and the highlight colour, click OK twice and you are
done.. This will not highlight the first occurrence of the duplicate value..

If you want to highlight all occurrences of the duplicates change the formula to

=COUNTIF(A:A,A1)>1

If your intention is to get a list with unique value, see
data>advanced filter, check unique records only and copy to another location..

--

Regards,

Peo Sjoblom

<rvh...@optonline.net> wrote in message news:3cdaa097...@news-server.optonline.net...
> If I have a spreadsheet of 500 values in one column and I sort it by
> ascending values, how can I make a maro or use a formatting command to
> highlight duplicate values? Any ideas. Thanks

Wilson

unread,

May 6, 2002, 11:44:43 AM5/6/02

to

You can use Conditional Formatting from the Menu, but code is more fun
Sub HighLiteDups()
Dim LastRow As Long
Dim cell As Range
Dim DataRng As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A1:A" & LastRow)
For Each cell In DataRng
If cell.Value = cell.Offset(1, 0).Value Then
cell.Interior.ColorIndex = 6
cell.Offset(1, 0).Interior.ColorIndex = 6
End If
Next cell
End Sub
HTH

rvh...@optonline.net

unread,

May 6, 2002, 11:51:09 AM5/6/02

to

Thanks Wilson, I got it to wrok exactly as you said. It's actually for
my brother. He will be home tonight and I will show him. I may need
you again with another question in the future.
Thanks again

On Mon, 6 May 2002 10:44:43 -0500, "Wilson" <jwi...@wickes.com>
wrote:

Greg Rivet

unread,

May 6, 2002, 1:33:55 PM5/6/02

to

Wilson, what if the data is not sorted and the dupes and not in consecutive
rows. TIA

Greg
"Wilson" <jwi...@wickes.com> wrote in message
news:O$uW9RR9BHA.1612@tkmsftngp07...

Wilson

unread,

May 6, 2002, 1:52:50 PM5/6/02

to

He said it was sorted, Greg. This code is worthless if it is not. Code
could be written to store the original positions of the data with a number
series in an adjacent column, copy the data to a blank column, sort it,
high-lite the duplicates and then put it all back the way it was - or, use
Chip's solutions at
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates
FWIW
"Greg Rivet" <greg...@hotmail.com> wrote in message
news:eSam7QS9BHA.2204@tkmsftngp04...

Wilson

unread,

May 6, 2002, 2:22:34 PM5/6/02

to

Or, you could use a little routine like this:
Public LastRow As Long
Public DataRng As Range
Public cell As Range
Public i As Long
Public Val As Variant
Sub FindValues()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A1:A" & LastRow)
For Each cell In DataRng

Val = cell.Value
Call DupeHiLite
Next cell
End Sub
Sub DupeHiLite()
If WorksheetFunction.CountIf(DataRng, Val) > 1 Then
Debug.Print Val

For Each cell In DataRng

If cell.Value = Val Then
cell.Interior.ColorIndex = 6
End If
Next cell
End If
Val = ""
End Sub
HTH

"Greg Rivet" <greg...@hotmail.com> wrote in message
news:eSam7QS9BHA.2204@tkmsftngp04...

rvh...@optonline.net

unread,

May 6, 2002, 4:46:13 PM5/6/02

to

Wilson,
Thank you for the help again, but this time, I have the same
single column of values but the values look like this:

Doe, Jon
Doe, John
Dog, Chris
Dorth, Stacey

Both the first and last name are in a sinlge cell separated by a
comma. Is there a way to highlight the cell if the last name is a
duplicate, only the name up to thte comma?
Example, in the list above, how would I write the code so the first
two entries would be highlighted because the last names are the same,
basically duplicates.

Thanks again,
Bob

On Mon, 6 May 2002 10:44:43 -0500, "Wilson" <jwi...@wickes.com>
wrote:

>You can use Conditional Formatting from the Menu, but code is more fun

Peo Sjoblom

unread,

May 6, 2002, 5:39:27 PM5/6/02

to

See my previous answer to your post on how to implement this and change the formula is to

=COUNTIF(A:A,"="&LEFT(A1,FIND(",",A1))&"*")>1

will highlight in this case the first 2 rows..

alternative formula

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT(A1,FIND(",",A1)),$A$1:$A$30)))*1)>1

adapt to fit your actual data

--
Regards,

Peo Sjoblom

<rvh...@optonline.net> wrote in message news:3cd6ea01...@news-server.optonline.net...

rvh...@optonline.net

unread,

May 6, 2002, 6:01:16 PM5/6/02

to

Thanks Peo, it worked exactly as I needed so far. I would like to do
it in a macro though so I don't have to type that formula in on every
worksheet. Can I do it in code and a macro?

On Mon, 6 May 2002 17:39:27 -0400, "Peo Sjoblom" <ter...@mvps.org>
wrote:

Greg Rivet

unread,

May 7, 2002, 12:50:24 PM5/7/02

to

Thank you Wilson

Greg
"Wilson" <jwi...@wickes.com> wrote in message

news:#pYeKqS9BHA.1304@tkmsftngp07...