Search for duplicates in a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to figure out how to search through column B of Sheet1. If
there are duplicates, display a message stating such, with the duplicate
value contained in the msgbox.

ideas?
 
Hi,

I am trying to figure out how to search through column B of Sheet1. If
there are duplicates, display a message stating such, with the duplicate
value contained in the msgbox.

ideas?
Click to expand...

Why not sort the data, and then compare one cell with the next?

For example

Dim currCell
Dim nextCell
Dim counter
Dim a

counter = activesheet.range("b2").currentregion.rows.count 'or use
usedrange...

for a = 1 to counter
currCell = range("b" & a).value
nextCell = range("b" & a).offset(1,0).value
if currCell = nextCell then
msgbox prompt:="You have a duplicate." & vbcr _
& "The value is " & currCell & vbcr _
& "The location is " a & " & " & a + 1, buttons:=vbOkOnly
end if
next

Matt
 
Hi,

I am trying to figure out how to search through column B of Sheet1. If
there are duplicates, display a message stating such, with the duplicate
value contained in the msgbox.

ideas?
Click to expand...

Why not sort the data and then compare one cell with the other?

for example (not tested)

dim currCell
dim nextCell
dim counter
dim a

counter = activesheet.range("b1").currentregion.rows.count 'or use
usedrange...

for a = 1 to counter
currCell = range("b" & a).value
nextCell = range("b" & a).offset(1,0).value

if currCell = nextCell then
msgbox prompt:="You have a duplicate." & vbcr _
& "The value is " & currCell & vbcr _
& "The location is " & a & " & " a + 1,
buttons:=vbokonly
end if
next

matt
 
Back
Top