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

There goes you're calc speed!
....

Good point. The only way to really make this fast would be to sort the
table in Sheet1 on a 3-column sort key: column C in ascending order
first, column A in ascending order second, and column D in ascending
order third. The column C entries < "" will sort to the top, and it's
then simple to generate the Sheet 2 result.

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

Sheet1!G3:
=IF(AND(C3<"",A3&"_"&D3<A2&"_"&D2),A3&"_"&D3,"")

Fill Sheet1!G3 down. Leave Sheet2 formulas as-is.

If the original Sheet1 table order is needed, in which case sorting
isn't feasible, use two columns of intermediate formulas in Sheet1.

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

Sheet1!H2:
=C2<""

Sheet1!H2:
=IF(C3<"",ISNA(MATCH(G3,G$2:G2,0)))

Fill Sheet1!G2 down into Sheet1!G3, then fill Sheet1!G3:H3 down until
the column H formulas return #N/A or #REF!. This will be somewhat
slow, but faster than using COUNTIF.

Change the Sheet2 column F formulas.

Sheet2!F2:
=MATCH(TRUE,Sheet1!$H$2:$H$27,0)

Sheet2!F3:
=MATCH(TRUE,INDEX(Sheet1!$H$2:$H$10000,F2+1):Sheet 1!$H$10000,0)+F2

Fill Sheet2!F3 down.