=SUMPRODUCT(--($A$1:$A$2000=$A$2:$A$2001),--($C$1:$C$2000=$C$2:$C$2001),--($D$1:$D$2000=$D$2:$D$2001))

will give you the count if you have values in all the rows. (if you rows
15 to 21 were all blank, for example, then this would show them as matching -
which technically, they are).

--
Regards,
Tom Ogilvy



"BN-CD" wrote:


Is there a better way to achieve the same result that doesn't take so
long. I let this run for 10 minutes before stopping it. I changed the
LastRow to = 50 and it ran fine so I know it works. Normally when
running this there will be between 2000 and 10000 rows to loop through.
I know when looping through that many rows it's going to take some time
but if 2000 rows takes longer than 10 minutes then this won't work well
for me.

Ok, This is seeing if 2 rows in columns A match then 2 rows in columns
C match then 2 rows in columns D match. If all are true then add 1 to
previous rows value. if its not true then put a 1 in the cell.
I'm basically counting the number of times columns A, C, and D match
with previous row then starting the count over.



Code:
--------------------
Dim counter As Integer
LastRow = Range("A65532").End(xlUp).Row
counter = 2
Do Until counter = LastRow + 1
If Range("A" & counter) = Range("A" & counter - 1) And Range("D" & counter) = Range("D" & counter - 1) And Range("C" & counter) = Range("C" & counter - 1) Then
Range("G" & counter) = Range("G" & counter - 1).Value + 1
Else
Range("G" & counter) = 1
End If
counter = counter + 1
Loop
--------------------


--
BN-CD
------------------------------------------------------------------------
BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374
View this thread: http://www.excelforum.com/showthread...hreadid=570815