Duplicate values

[visible2you]

I want to modify following program : -


' Source: http://www.vbaexpress.com/forum/showthread.php?t=10600


Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim rng As Range

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If .Evaluate("SUMPRODUCT(--(A" & i & ":A" & iLastRow & "=A" & i & ")," & _
"--(B" & i & ":B" & iLastRow & "=B" & i & ")," & _
"--(C" & i & ":C" & iLastRow & "=C" & i & "))") > 1 Then
If rng Is Nothing Then
Set rng = .Cells(i, "A").Resize(, 3)
Else
Set rng = Union(rng, .Cells(i, "A").Resize(, 3))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete

End With

End Sub


The reason why I could not use aforesaid program is that I do not want to delete duplicate ROW(s).

:think: :think: :think:

My scenario is different:-

In an Excel file, there are two sheets:-

1. Sheets(1)
2. Sheets(2)

I am not allowed to change entire contents of Sheet(1), unless to add a unique and new row from Sheet(2) or vice-versa.

I am allowed to comment on contents of Sheet(1) or Sheet (2) in Column J only on basis of Col A, B and D.

Lets Imagine that Column J of both worksheets do not exist.

Based on either worksheet, We have to update values in Column J of both worksheets through VBA.

Priority (in order) to update sheets:-

Sheets(2) first and then Sheets(1)

It would mean that we have to start filling J Col of Sheet(2) first.

I want to comment as per following definitions: -

Duplicate in Sheet2: Those duplicate values in Col A, B and D of Sheets(2)
Existing in Sheet1: Those values which are same in Col A, B and D of Sheets(1) and Sheets(2).
Existing in Sheet2: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1)
Updated from Sheet2: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1) and were updated in Sheets(1) using data from Sheets(2).
Updated from Sheet1: Those values in Col A, B and D of Sheets(1) which does not exist in Sheets(2) and were attached in Sheets(2) using data from Sheets(1).

So, aforesaid comments are only applicable.

: pray2:

Lets say that if In Sheets(2), in [Row 1 of Col 1, 2 and 4] and [Row 3 of Col 1, 2 and 4] contain same data.

Then in Row1, Col 10 ... I will comment as "Existing in Sheet1".

Then in Row 3, Col 10 ... I will comment as "Duplicate in Sheet2".

There were multiple scenarios as mentioned above.