How can I use excel to find consecutive duplicate entries in a column?


e.g,
Col B
1614
1522
1522 duplicate consecutive entry
how can I use excel to flag this for me?

Thanks,

Liz Merrill

Hi
see:
http://www.cpearson.com/excel/duplic...gingDuplicates

--
Regards
Frank Kabel
Frankfurt, Germany

"Lizmerrill" schrieb im Newsbeitrag
...
How can I use excel to find consecutive duplicate entries in a
column?


e.g,
Col B
1614
1522
1522 duplicate consecutive entry
how can I use excel to flag this for me?

Thanks,

Liz Merrill

Here is how I did that:

Sub RemoveDuplicates()

Dim totalrows As Integer
Dim nameColumn As Integer
Dim count As Integer
Dim Row As Integer

totalrows = ActiveSheet.UsedRange.Rows.count
count = 0
nameColumn = 1
For Row = totalrows To 6 Step -1

' I had some empty names that did not need to be deleted
If Cells(Row, nameColumn).Value < Empty Then

If Cells(Row, nameColumn).Value = Cells(Row - 1, nameColumn).Value
Then
Rows(Row).Delete
count = count + 1
End If
End If
Next Row
If ShowMsgs Then
MsgBox "Rows deleted equals " & count
End If

Probably not as elegant as it could have been, but hopefully it is
understanable and helps.

Ken Loomis


"Lizmerrill" wrote in message
...
How can I use excel to find consecutive duplicate entries in a column?


e.g,
Col B
1614
1522
1522 duplicate consecutive entry
how can I use excel to flag this for me?

Thanks,

Liz Merrill

I gave you code to delete the duplicates before I realized you just wanted
to flag them.

Sorry.

Ken Loomis


"Ken Loomis" wrote in message
...
Here is how I did that:

Sub RemoveDuplicates()

Dim totalrows As Integer
Dim nameColumn As Integer
Dim count As Integer
Dim Row As Integer

totalrows = ActiveSheet.UsedRange.Rows.count
count = 0
nameColumn = 1
For Row = totalrows To 6 Step -1

' I had some empty names that did not need to be deleted
If Cells(Row, nameColumn).Value < Empty Then

If Cells(Row, nameColumn).Value = Cells(Row - 1, nameColumn).Value
Then
Rows(Row).Delete
count = count + 1
End If
End If
Next Row
If ShowMsgs Then
MsgBox "Rows deleted equals " & count
End If

Probably not as elegant as it could have been, but hopefully it is
understanable and helps.

Ken Loomis


"Lizmerrill" wrote in message
...
How can I use excel to find consecutive duplicate entries in a column?


e.g,
Col B
1614
1522
1522 duplicate consecutive entry
how can I use excel to flag this for me?

Thanks,

Liz Merrill

To simply flag the 2nd one, use conditional formatting: select the cells in
column B, with B2 active, Format/Conditional Formatting, Formula Is, and type
=B1; then set your formatting.

On 06 Oct 2004 19:25:39 GMT, (Lizmerrill) wrote:

How can I use excel to find consecutive duplicate entries in a column?


e.g,
Col B
1614
1522
1522 duplicate consecutive entry
how can I use excel to flag this for me?

Thanks,

Liz Merrill