The most efficient way using formulas...

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,RO WS(F$2:F2)),B:B)),"")

Copy down until you get blanks.

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

--
Biff
Microsoft Excel MVP


wrote in message
...
On Apr 23, 10:18 pm, Teethless mama
wrote:
Try this:

=INDEX(rngA&"_"&rngD,SMALL(IF(rngC<"",ROW(INDIREC T("1:"&ROWS(rngC)))),ROWS($1:1)))

ctrl+shift+enter, not just enter


Thanks for your reply.
Your formula works fine, except for the fact that it takes my PC
forever and ever to do the calculations! Right now, I have approx.
600 records in Sheet1. (The number of records in Sheet1 varies
daily.) What I have done is copy your array formula down Column A of
Sheet2, from row 2 to row 1000. But the sand clock stays on forever
and ever...

Can your formula be optimized?
--
tb