I've got a very large (50,000 rows) excel spreadsheet with some product cross
reference data on it.

I'd like to find out if we have duplicate entries on there or not. Is there
a macro we can write to do the following?

I want excel to put an "x" in the first column (a) of the spreadsheet on the
row when the contents of colum (e) AND column (f) match the contents of the
NEXT row...

Example:

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----___-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

AFTER THE MACRO;

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----_X_-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

I will of course sort the database so items that are the same would be next
to each other.

This would save my eyballs tremendously!

Thanks for your help!

Try this as a workbook module, works on the active sheet:-

Sub tagduplicates()
lastrow = Range("E65536").End(xlUp).Row
For x = lastrow To 2 Step -1
e = Cells(x, 5).Value
f = Cells(x, 6).Value
e1 = Cells(x - 1, 5).Value
f1 = Cells(x - 1, 6).Value
If e < "" And e = e1 And f = f1 Then
Cells(x - 1, 1).Value = "X"
End If
Next
End Sub


Does that work?

Mike


"David B" wrote:

I've got a very large (50,000 rows) excel spreadsheet with some product cross
reference data on it.

I'd like to find out if we have duplicate entries on there or not. Is there
a macro we can write to do the following?

I want excel to put an "x" in the first column (a) of the spreadsheet on the
row when the contents of colum (e) AND column (f) match the contents of the
NEXT row...

Example:

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----___-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

AFTER THE MACRO;

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----_X_-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

I will of course sort the database so items that are the same would be next
to each other.

This would save my eyballs tremendously!

Thanks for your help!

Why not ...

=IF(AND(E2=E3,F2=F3),"X","") and copy(/paste) down

"David B" wrote:

I've got a very large (50,000 rows) excel spreadsheet with some product cross
reference data on it.

I'd like to find out if we have duplicate entries on there or not. Is there
a macro we can write to do the following?

I want excel to put an "x" in the first column (a) of the spreadsheet on the
row when the contents of colum (e) AND column (f) match the contents of the
NEXT row...

Example:

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----___-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

AFTER THE MACRO;

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----_X_-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

I will of course sort the database so items that are the same would be next
to each other.

This would save my eyballs tremendously!

Thanks for your help!

WOW!!!

That worked exactly as I had envisioned it. A very elegant solution.

Thank you for taking the time to help me.

David

"Mike H" wrote:

Try this as a workbook module, works on the active sheet:-

Sub tagduplicates()
lastrow = Range("E65536").End(xlUp).Row
For x = lastrow To 2 Step -1
e = Cells(x, 5).Value
f = Cells(x, 6).Value
e1 = Cells(x - 1, 5).Value
f1 = Cells(x - 1, 6).Value
If e < "" And e = e1 And f = f1 Then
Cells(x - 1, 1).Value = "X"
End If
Next
End Sub


Does that work?

Mike


"David B" wrote:

I've got a very large (50,000 rows) excel spreadsheet with some product cross
reference data on it.

I'd like to find out if we have duplicate entries on there or not. Is there
a macro we can write to do the following?

I want excel to put an "x" in the first column (a) of the spreadsheet on the
row when the contents of colum (e) AND column (f) match the contents of the
NEXT row...

Example:

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----___-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

AFTER THE MACRO;

HEAD-----_A_-----_B_-----_C_-----_D_-----_E_-----_F_-----_G_
Row1-----___-----nnn-----___-----___-----ABC-----FKL-----___
Row2-----_X_-----ppp-----rew-----TJL-----GGG----ZZZ-----___
Row3-----___-----ppp-----rew-----RPJ-----GGG----ZZZ-----___
Row4-----___-----dpw-----hdc-----TJL-----PQV----ZZZ-----___

I will of course sort the database so items that are the same would be next
to each other.

This would save my eyballs tremendously!

Thanks for your help!