COUNTIF(G$2:G2,...)
Fill Sheet1!G3 down into Sheet1!G4:G10000

There goes you're calc speed!

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
wrote...
....
. . . except that I am getting duplicate
records in Column A (of Sheet2) whenever two or more records (of
Sheet1) have the same data in Columns A and D. . . .

Do you realize this is the first time you mentioned that you could
have duplicate records and that you don't seem to want duplicate
records?

We can't read your mind, so you should state all your requirements in
your original post.

. . . I suspect that the
problem is with the formula that goes into F3 and then is copied down,
but I am too much of a novice to figure out by myself how to modify
it. Hopefully you, or somebody else, can help me out...

If you want Sheet2 to show only distinct values from Sheet1, then the
only sensible way to do this is to add formulas in another column in
Sheet1 to show only the first of each possibly duplicated value when
the column C value < "".

With your data in Sheet1!A2:D10000, I'll assume you could add formulas
in Sheet1!G2:G10000. Modify as needed.

Sheet1!G2:
=IF(C2<"",A2&"_"&D2,"")

Sheet1!G3:
=IF(AND(C3<"",COUNTIF(G$2:G2,A3&"_"&D3)=0),A3&"_" &D3,"")

Fill Sheet1!G3 down into Sheet1!G4:G10000. Change Sheet2 formulas in
column F.

Sheet2!F2:
=MATCH("?*",Sheet1!$G$2:$G$27,0)

Sheet2!F3:
=MATCH("?*",INDEX(Sheet1!$G$2:$G$10000,F2+1):Sheet 1!$G$10000,0)+F2

Fill Sheet2!F3 down until the formulas return either #N/A or #REF!.