"T. Valko" wrote...
The most efficient way using formulas...

Requires multiple cells per individual result.

Let's assume this formula is on sheet2 starting in A2:

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

Let's assume that formula is in the range A2:A100.

Enter this formula in B2 and copy down to B100:

=IF(A2="","",ROW())

Let's get a count of how many records meet the condition. Enter this formula
in, say, F1:

=COUNT(B:B)

Now, let's get the records in a contiguous range. Enter this formula in F2:

=IF(ROWS(F$2:F2)<=F$1,INDEX(A:A,MATCH(SMALL(B:B,R OWS(F$2:F2)),B:B)),"")

Copy down until you get blanks.

You can hide columns A and B if you'd like.

As I said above, it requires multiple cells per individual result.

There's a more efficient way. If the first result should be in cell A2
and you could use col F for supporting formulas, try

F2 [array formula]:
=MATCH(TRUE,(Sheet1!$C$2:$C$10000<""),0)

A2:
=INDEX(Sheet1!$A$2:$A$10000,F2)&"_"&INDEX(Sheet1!$ D$2:$D$10000,F2)

F3 [array formula]:
=MATCH(TRUE,(INDEX(Sheet1!$C$2:$C$10000,F2+1):Shee t1!$C$10000<0),
0)+F2

Fill F3 down until the formulas return #REF!. Clear cells returning
#REF!. Then fill A2 down into the rows with formulas in column F.

OTOH, if the source data is relatively static, the OP may find it
preferable to add a column of formulas to the source data, something
like

X2:
=IF(C2<"",A2&"_"&D2,"")

Then select the column X range (including row 1), run an autofilter,
and filter only Nonblank cells. This will hide the cells evaluating to
"", leaving only the desired results. Copy, then paste into Sheet2!A1.
This will paste only the filtered rows from Sheet1 into Sheet2.