Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
"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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
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!A2:D10000, 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):Sheet 1!$G$10000,0)+F2 Fill Sheet2!F3 down until the formulas return either #N/A or #REF!. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
COUNTIF(G$2:G2,...)
Fill Sheet1!G3 down into Sheet1!G4:G10000 There goes you're calc speed! -- Biff Microsoft Excel MVP "Harlan Grove" wrote in message ... 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!A2:D10000, 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):Sheet 1!$G$10000,0)+F2 Fill Sheet2!F3 down until the formulas return either #N/A or #REF!. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
"T. Valko" wrote...
COUNTIF(G$2:G2,...) Fill Sheet1!G3 down into Sheet1!G4:G10000 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):Sheet 1!$H$10000,0)+F2 Fill Sheet2!F3 down. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need Improved String Formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
improved chart in Excel | Charts and Charting in Excel | |||
Converting a Formula stored as String to real Formula | Excel Worksheet Functions | |||
separate cell values with formulas - can this be improved? | Excel Worksheet Functions | |||
Improved Excel functions curve fitting in sheet | Excel Worksheet Functions | |||
[XL] Edit Mode of formulae should be improved in legibility. | Excel Worksheet Functions |