"Northwoods" wrote
.. Max, I need to know a quick way to copy the formula
down to the 22,000 row. I was dragging down and then
I thought hey there must be a better way. Any tips?

99% of the time, I'd just simply copy the formula/s by dragging down.

Anyway, here's an alternative to dragging down. Since we're dealing with
large ranges and calc intensive formulas here, as a precaution, I'd set the
book's calc mode to "Manual" first, via clicking Tools Options
Calculation tab Check "Manual" OK.

Eg for:

Put in A2:
=IF(COUNTBLANK(X!A2:C2)=3,"",IF(SUMPRODUCT((X!$A$2 :A2=X!A2)*(X!$B$2:B2=X!B2)*(X!$C$2:C2=X!C2))<2,ROW (),""))
Copy A2 down to cover the max expected extent of source data,
say down to A22200.

Type the required range in the namebox, viz.: A2:A22200
Press ENTER. This will select the range with A2 active

Then with range selected, copy & paste the formula directly into the formula
bar (this will be for A2, the active cell) and press CTRL+ENTER (instead of
just pressing ENTER). This will fill the formulas for the entire range
A2:A22200

As for the other fill down for a multi-col range ..

Then put in B2:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMAL L($A:$A,ROW(A1)),$A:$A,0)))
Copy B2 to G2. Select B2:G2, fill down by the smallest extent sufficient
to
cover the max expected number of unique lines from X, say down to G5000.

After copying B2 to G2, just right-click to copy the range B2:G2
Then type the reqd range in the name box, ie: B3:G5000
press ENTER (B3 will be active). Then right-click on the selected range,
choose Paste special check "Formulas" OK.

And after filling all of the above / whenever required, we should remember
to press F9 to calculate.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---