Need Improved String Formula
On Apr 24, 2:24*pm, Harlan Grove wrote:
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.
Thank you, Harlan, for your suggestion.
Your formulas are indeed fast, 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. 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...
--
tb
|