Finding Duplicate Rows (Macro)
 

Hi,

I have a table with several rows and columns of information. I will
continue to add new information new rows and I want to make sure that I never
have duplicated a row.

So here is a simple example:

A B C
1 i 1 k

2 r 3 n

3 a 2 f

4 a 1 f


I want to be able to add another row of information and if the array in that
row from columns a to c matches an array I already have I want to be notified
and it would also be nice to know which array I am duplicating.

I would like to accomplish the task above, but using the VBA tool (macros).
Ideally I would get the Item Number (located in the first column) of the
array Im duplicating in a message box.

Any help would be greatly appreciated!

Regards,

--
Edwin

Finding Duplicate Rows (Macro)
 

Here's a simplistic macro that might get you started. After inputting your
data, and having selected a cell in the row you want to check, you could run
this.

Sub FindDuplicate()
xEnd = ActiveCell.Row

For i = 1 To xEnd - 1
If Cells(i, 1).Value = Cells(xEnd, 1) Then
If Cells(i, 2).Value = Cells(xEnd, 2) Then
If Cells(i, 3).Value = Cells(xEnd, 3) Then
MsgBox "Row " & i & " contains duplicate entry.", vbOKOnly, _
"Duplicate Found"
Exit Sub
End If
End If
End If
Next
MsgBox "No duplicate found", vbOKOnly, "No Duplicates"
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Edwin" wrote:

Hi,

I have a table with several rows and columns of information. I will
continue to add new information new rows and I want to make sure that I never
have duplicated a row.

So here is a simple example:

A B C
1 i 1 k

2 r 3 n

3 a 2 f

4 a 1 f


I want to be able to add another row of information and if the array in that
row from columns a to c matches an array I already have I want to be notified
and it would also be nice to know which array I am duplicating.

I would like to accomplish the task above, but using the VBA tool (macros).
Ideally I would get the Item Number (located in the first column) of the
array Im duplicating in a message box.

Any help would be greatly appreciated!

Regards,

--
Edwin

Finding Duplicate Rows (Macro)
 

Thanks Luke!! This is a great start!

Do you think it is possible to obtain the location of more than one
duplicated row in the table? Maybe using a loop or something like that??

--
Edwin


"Luke M" wrote:

Here's a simplistic macro that might get you started. After inputting your
data, and having selected a cell in the row you want to check, you could run
this.

Sub FindDuplicate()
xEnd = ActiveCell.Row

For i = 1 To xEnd - 1
If Cells(i, 1).Value = Cells(xEnd, 1) Then
If Cells(i, 2).Value = Cells(xEnd, 2) Then
If Cells(i, 3).Value = Cells(xEnd, 3) Then
MsgBox "Row " & i & " contains duplicate entry.", vbOKOnly, _
"Duplicate Found"
Exit Sub
End If
End If
End If
Next
MsgBox "No duplicate found", vbOKOnly, "No Duplicates"
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Edwin" wrote:

Hi,

I have a table with several rows and columns of information. I will
continue to add new information new rows and I want to make sure that I never
have duplicated a row.

So here is a simple example:

A B C
1 i 1 k

2 r 3 n

3 a 2 f

4 a 1 f


I want to be able to add another row of information and if the array in that
row from columns a to c matches an array I already have I want to be notified
and it would also be nice to know which array I am duplicating.

I would like to accomplish the task above, but using the VBA tool (macros).
Ideally I would get the Item Number (located in the first column) of the
array Im duplicating in a message box.

Any help would be greatly appreciated!

Regards,

--
Edwin