This took about two hours:
Sub CalcH()
Dim rng As Range
Dim rng2 As Range
Dim sngStart As Single
Dim sForm As String
sngStart = Timer
Set rng = Range(Cells(1, 2), _
Cells(Rows.Count, 2).End(xlUp))
Set rng2 = Range(Cells(1, 6), _
Cells(Rows.Count, 6).End(xlUp))

sForm = "(" & rng.Address & "=F1)*(" & _
rng.Offset(0, 1).Address & "=G1))"
rng2.Offset(0, 2).Formula = "=If(SUMPRODUCT(" & _
sForm & "0,1,na())"
On Error Resume Next
rng2.Offset(0, 2).SpecialCells(xlFormulas, _
xlErrors).ClearContents
On Error GoTo 0
rng2.Offset(0, 2).Formula = rng2.Offset(0, 2).Value
Cells(1, "J").Value = Timer - sngStart
ThisWorkbook.Save
End Sub

---

I checked the results manually and that took about 30 minutes with over
60000 rows. But I had to use a couple of dummy columns past column H. Is
this acceptable? Can one make temporary use of columns past column H.

--
Regards,
Tom Ogilvy



ChrisG wrote in message
...
Faster would be better....I ran subroutine and it was
still churning when I left the office maxing out hte
CPU. Hmmmmh.

What I want for example:
For each value in Col B & Col C compare to every record
pair in Col F & Col G respectively when B = F AND C = G
then put a "1" in H. I thought by indexing through B & C
first comparing to each F & G using Do/For loop to handle
discretely would be effecient.....Would an array
construct be more effecient??


-----Original Message-----
Dim i As Long
Dim j As Long

i = 1
j = 1

Do While Not (IsEmpty(Cells(i, 2)))
Do While Not (IsEmpty(Cells(j, 6)))
If Cells(i, 2) = Cells(j, 6) And _
Cells(i, 3) = Cells(j, 7) _
Then Cells(j, 8) = "1"
j = j + 1
Loop
j = 1
i = i + 1
Loop


You want to put a 1 in any row j (column H) where the
values in columns F
and G match the values in Columns B and C respectively
with the B and C
values being in row i.

this would require 54000 x 54000 comparisons. I your
description, however,
you sound like you only want a 1 if all on the same row,
B = F and C = G.
If so, you don't need a nested loop


Dim i As Long
i = 1
Do While Not (IsEmpty(Cells(i, 2)))
and Not (IsEmpty(Cells(j, 6)))
If Cells(i, 2) = Cells(i, 6) And _
Cells(i, 3) = Cells(i, 7) _
Then Cells(i, 8) = "1"
i = i + 1
Loop

If you post back with exactly what you want to check and
do, then I am sure
we can suggest a faster way.

--
Regards,
Tom Ogilvy



ChrisG wrote in
message
...
I get this code to work for small lists, however, the
real test is on almost 54,000 records and the code
crashes when j = 32,767. In a nutshell Col B and Col C
have values and are being compared against Col F and
Col
G (If B=F AND C=G, then H=1)

Dim i As Integer
Dim j As Integer

i = 1
j = 1

Do While Not (IsEmpty(Cells(i, 2)))
i = i + 1
Do While Not (IsEmpty(Cells(j, 6)))
j = j + 1
If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i,
3)
= Cells(j, 7))) Then Cells(j, 8) = "1"
Loop
j = 1
Loop



.