Need Improved String Formula

[Tiziano]

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.

 

[Teethless mama]

Try this:

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

ctrl+shift+enter, not just enter

 

[tiziano1]

Try this:

=INDEX(rngA&"_"&rngD,SMALL(IF(rngC<>"",ROW(INDIRECT("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?

 

[T. Valko]

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,ROWS(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

Try this:

=INDEX(rngA&"_"&rngD,SMALL(IF(rngC<>"",ROW(INDIRECT("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?

 

[Harlan Grove]

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,ROWS(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):Sheet1!$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.

 

[tiziano1]

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):Sheet1!$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...

 

[Harlan Grove]

(e-mail address removed) wrote...
....

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

Do you realize this is the first time you mentioned that you could
have duplicate records and that you don't seem to want duplicate
records?

We can't read your mind, so you should state all your requirements in
your original post.

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

If you want Sheet2 to show only distinct values from Sheet1, then the
only sensible way to do this is to add formulas in another column in
Sheet1 to show only the first of each possibly duplicated value when
the column C value <> "".

With your data in Sheet1!A210000, I'll assume you could add formulas
in Sheet1!G2:G10000. Modify as needed.

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

Sheet1!G3:
=IF(AND(C3<>"",COUNTIF(G$2:G2,A3&"_"&D3)=0),A3&"_"&D3,"")

Fill Sheet1!G3 down into Sheet1!G4:G10000. Change Sheet2 formulas in
column F.

Sheet2!F2:
=MATCH("?*",Sheet1!$G$2:$G$27,0)

Sheet2!F3:
=MATCH("?*",INDEX(Sheet1!$G$2:$G$10000,F2+1):Sheet1!$G$10000,0)+F2

Fill Sheet2!F3 down until the formulas return either #N/A or #REF!.

 

[T. Valko]

COUNTIF(G$2:G2,...)

Fill Sheet1!G3 down into Sheet1!G4:G10000

There goes you're calc speed!

--
Biff
Microsoft Excel MVP


(e-mail address removed) wrote...
....

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

Do you realize this is the first time you mentioned that you could
have duplicate records and that you don't seem to want duplicate
records?

We can't read your mind, so you should state all your requirements in
your original post.

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

If you want Sheet2 to show only distinct values from Sheet1, then the
only sensible way to do this is to add formulas in another column in
Sheet1 to show only the first of each possibly duplicated value when
the column C value <> "".

With your data in Sheet1!A210000, I'll assume you could add formulas
in Sheet1!G2:G10000. Modify as needed.

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

Sheet1!G3:
=IF(AND(C3<>"",COUNTIF(G$2:G2,A3&"_"&D3)=0),A3&"_"&D3,"")

Fill Sheet1!G3 down into Sheet1!G4:G10000. Change Sheet2 formulas in
column F.

Sheet2!F2:
=MATCH("?*",Sheet1!$G$2:$G$27,0)

Sheet2!F3:
=MATCH("?*",INDEX(Sheet1!$G$2:$G$10000,F2+1):Sheet1!$G$10000,0)+F2

Fill Sheet2!F3 down until the formulas return either #N/A or #REF!.

 

[Harlan Grove]

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):Sheet1!$H$10000,0)+F2

Fill Sheet2!F3 down.

 

[Tiziano]

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!