Check for duplicate numbers 1

[stussy]

Hi

Wondered if anyone had any ideas that would help here. We import items into our epos application from excel sheets. Every week we find that one supplier or another has included the same barcode on multiple products, which crashes out the import routine, and we then have to sort the sheet by barcode, and look for the duplicates.

Any good ideas for automating this task? Something that just searches one column for duplicate values?

Cheers

Mike

 

[AnotherHiggins]

Here’s a quick little macro that will highlight any duplicates (including the first occurrence):

Sub FindDupes()
ActiveCell.EntireColumn.Select
myColumn = Mid(ActiveCell.Address, 2, (InStr(2, ActiveCell.Address, "$")) - 2)
myCell = Replace(ActiveCell.Address, "$", "")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=COUNTIF(" & myColumn & ":" & myColumn & "," & myCell & ")>1"
Selection.FormatConditions(1).Interior.ColorIndex = 6
End Sub

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[stussy]

Fastest useful reply I have ever seen on TT! You have just made our epos admin's life 50 times easier! Cheers, have a beer on me later...well failing that have a star...

 

[AnotherHiggins]

Dang, I'd really like that beer.

Oh well, glad I could help.


[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.