Nested Do While Loop to Find and Highlight Duplicates

Explained in Hindi

Nested Do While loops are one of the most powerful macros tools to calculate or analyze your Excel data. You can find, highlight and/or delete duplicates using such loops in a loop. The outer loop first catches one row of data. The inner loop then compares the rest of the data in the following rows with the row defined in the outer row. In this manner a comparision can be made and if the data is 'equal' it can be detected, highlighted and/or deleted.
The code of such a macro looks like this:
Sub find_duplicates()
r = 5
c = r + 1
Do While Cells(r, 1) <> ""
Do While Cells(c, 1) <> ""
If Cells(r, 1) = Cells(c, 1) And Cells(r, 4) = Cells(c, 4) Then
Cells(r, 1).Font.ColorIndex = 3
Cells(c, 1).Font.ColorIndex = 3
Cells(c, 4).Font.ColorIndex = 3
Cells(r, 4).Font.ColorIndex = 3
Cells(c, 5).Font.Bold = True
Cells(c, 5) = "Duplicate"
End If
c = c + 1
r = r + 1
c = r + 1
End Sub
You can view the online training video to understand the code and further details.

Watch this training video on YouTube
Nested Do While Loop to Find and Highlight Duplicates