Sheet1 of my workbook has some data in columns A and D that I would like
to combine into a single text string, but only if data in Column C is <
"". The string should be made up in the following way: Data in column
A + Underscore sign + Data in column D.

As an example:
* Data in column A: abcdef
* Data in column D: 123456
* End result should be: abcdef_123456

The end result should be listed in Column A of Sheet2. And so, I have
created the following formula and copied it down Column A of Sheet2:

= if(Sheet1!c2<"",Sheet1!a2&"_"&Sheet1!d2,"")

The above formula works fine, except that it creates blank rows in
Sheet2 whenever a record in Sheet1 has data in Column C that is equal to "".

Can anyone suggest a formula that skips all those records in Sheet1
where data in Column C is equal to ""?

Thanks for your help.
--
tb

Try this:

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

ctrl+shift+enter, not just enter



"Tiziano" wrote:

Sheet1 of my workbook has some data in columns A and D that I would like
to combine into a single text string, but only if data in Column C is <
"". The string should be made up in the following way: Data in column
A + Underscore sign + Data in column D.

As an example:
* Data in column A: abcdef
* Data in column D: 123456
* End result should be: abcdef_123456

The end result should be listed in Column A of Sheet2. And so, I have
created the following formula and copied it down Column A of Sheet2:

= if(Sheet1!c2<"",Sheet1!a2&"_"&Sheet1!d2,"")

The above formula works fine, except that it creates blank rows in
Sheet2 whenever a record in Sheet1 has data in Column C that is equal to "".

Can anyone suggest a formula that skips all those records in Sheet1
where data in Column C is equal to ""?

Thanks for your help.
--
tb

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

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

"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.

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

on 4/23/2009 9:11 PM Tiziano wrote the following:
Sheet1 of my workbook has some data in columns A and D that I would like
to combine into a single text string, but only if data in Column C is <
"". The string should be made up in the following way: Data in column
A + Underscore sign + Data in column D.

As an example:
* Data in column A: abcdef
* Data in column D: 123456
* End result should be: abcdef_123456

The end result should be listed in Column A of Sheet2. And so, I have
created the following formula and copied it down Column A of Sheet2:

= if(Sheet1!c2<"",Sheet1!a2&"_"&Sheet1!d2,"")

The above formula works fine, except that it creates blank rows in
Sheet2 whenever a record in Sheet1 has data in Column C that is equal to "".

Can anyone suggest a formula that skips all those records in Sheet1
where data in Column C is equal to ""?

Thanks for your help.

Thanks everybody for the help!
--
tb