It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

I am trying to find duplicate values in an excel sheet from multiple columns (via VBA / Macros)

It works for the most part, however, there are a few ambiguities when I am doing a comparison between numeric values. Numbers need to be compared literally rather than the actual value.

Attached is an excel sheet.

## 1.

Rows 2 and 4 are correctly identified as duplicates.
## 2.

Rows 6 and 9 are WRONGLY identified as duplicates.
## 3.

Rows 7 and 8 are CORRECTLY identified as NOT duplicates. When we have a some value in Column D (which may or may not contain any values)
## 4.

Rows 10 and 11 are WRONGLY identified as duplicates inspite of having unique values (atleast on display)

Any help is appreciated to resolve the inconsistency.

Duplicate.xlsm

It works for the most part, however, there are a few ambiguities when I am doing a comparison between numeric values. Numbers need to be compared literally rather than the actual value.

Attached is an excel sheet.

Any help is appreciated to resolve the inconsistency.

Duplicate.xlsm

Is this what you are meaning?

.300 is different from .3000 (alteast visually). The same should hold true while comparing.

Range("H6").Text

Thank you, for some reason I was drawing a blank on that one. I think because I was thinking about the compare of row 6 and row 9. In $H$9 it seems to have dropped the .00 so the comparison will still return true.

So if A1 shows 1.2345 and A2 shows 1.23 only because of the number format (they are both 1.2345 behind the scenes)

If Range("A1").Text = Range("A2").Text Then

will be evaluated as False.

Dim RowCount As Integer

Dim RowCounter As Integer

Dim DupRow As Variant

Dim DuplicateErrorDetails As Variant

Dim r As Range

Dim i As Long

Dim DuplicateErrorCount As Variant

DuplicateErrorCount = 0

DuplicateErrorDetails = ""

ActiveSheet.Range("K4").Cl

ActiveSheet.Range("K4").Cl

'MsgBox ("in ValidateDuplicateLabCodes"

RowCount = ActiveSheet.Range("B:B").E

'MsgBox (RowCount)

' For RowCounter = 2 To RowCount + 1

' Range("A" & RowCounter).Interior.Color

' Range("I" & RowCounter).Value = ""

'''' =COUNTIF($AA$2:$AA$65536,A

'Range("I" & RowCounter).Formula = "=COUNTIF($H$2:$H$65536,H"

Set r = Range("H1").End(xlDown).Of

For RowCounter = 2 To r.Row

Range("A" & RowCounter).Interior.Color

Range("I" & RowCounter).Value = ""

For i = RowCounter + 1 To r.Row

If Range("H" & RowCounter).Text = Range("H" & i).Text Then

DupRow = DupRow + 1

End If

Next

If DupRow >= 1 Then

DuplicateErrorCount = DuplicateErrorCount + 1

Range("A" & RowCounter).Interior.Color

DuplicateErrorDetails = DuplicateErrorDetails & "Duplicate values found in row " & RowCounter & vbLf

DupRow = 0

End If

Next

' DupRow = Range("I" & RowCounter).Value

' MsgBox ("Value at AB" & RowCounter & " : " & DupRow)

' If DupRow > 1 Then

' DuplicateErrorCount = DuplicateErrorCount + 1

' Range("A" & RowCounter).Interior.Color

' DuplicateErrorDetails = DuplicateErrorDetails & "Duplicate values found in row " & RowCounter & vbLf

' End If

' DupRow = 0

' Range("I" & RowCounter).Value = ""

' Next RowCounter

If (DuplicateErrorDetails = "") Then

DuplicateErrorDetails = "No Error"

End If

ActiveSheet.Range("K4").Ho

ActiveSheet.Range("K4").Va

ActiveSheet.Range("K4").Ad

DuplicateErrorCount = 0

DuplicateErrorDetails = ""

End Sub

Can you please help?

Duplicate.xlsm

If we can restrict pasting of data in a certain format, even better.

How about adding

Columns("B:B").Select

Selection.NumberFormat = "@"

just above the following line?

Set r = Range("H1").End(xlDown).Of

Currently, we are highlighting / marking just the first occurrence of the duplicate values. Is it possible to include each and every duplicate value with the color?

There will be alteast 2000 rows which will need to be parsed. So, in that case, it will be helpful to see ANY AND ALL rows which are duplicated highlighted.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Dim RowCount As Integer

Dim RowCounter As Integer

Dim DupRow As Variant

Dim DuplicateErrorDetails As Variant

Dim r As Range

Dim i As Long

Dim DuplicateErrorCount As Variant

DuplicateErrorCount = 0

DuplicateErrorDetails = ""

ActiveSheet.Range("K4").Cl

ActiveSheet.Range("K4").Cl

RowCount = ActiveSheet.Range("B:B").E

Columns("B:B").Select

Selection.NumberFormat = "@"

Set r = Range("H1").End(xlDown).Of

For RowCounter = 2 To r.Row

Range("A" & RowCounter).Interior.Color

Next

For RowCounter = 2 To r.Row

Range("I" & RowCounter).Value = ""

For i = RowCounter + 1 To r.Row

If Range("H" & RowCounter).Text = Range("H" & i).Text Then

DupRow = DupRow + 1

Range("A" & i).Interior.ColorIndex = 31

End If

Next

If DupRow >= 1 Then

DuplicateErrorCount = DuplicateErrorCount + 1

' Range("A" & RowCounter).Interior.Color

DuplicateErrorDetails = DuplicateErrorDetails & "Duplicate values found in row " & RowCounter & vbLf

DupRow = 0

End If

Next

If (DuplicateErrorDetails = "") Then

DuplicateErrorDetails = "No Error"

End If

ActiveSheet.Range("K4").Ho

ActiveSheet.Range("K4").Va

ActiveSheet.Range("K4").Ad

DuplicateErrorCount = 0

DuplicateErrorDetails = ""

End Sub