| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula using array to find embedded value
Im using the following formula to determine if a set of values (CoreNames)
are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Replace the "Y" (including quotes) with 'Corename'
=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Thanks very much for the quick reply.
I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
How is "corename" defined? Is it a named range?
"Ted Horsch" wrote: Thanks very much for the quick reply. I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Ignore my last reply as the answer is in your first posting!
"Toppers" wrote: How is "corename" defined? Is it a named range? "Ted Horsch" wrote: Thanks very much for the quick reply. I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Set up Corindex" in A5 to A8 with numbers 1,2 3, 4
=IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&" *"),(Coreindex)),$A$1,$A$2,$A$3,$A$4),"N") I am sure there is a more elegant solution but this is my best shot! "Ted Horsch" wrote: Thanks very much for the quick reply. I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Better ....
=CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(C oreindex))+1,"N",$A$1,$A$2,$A$3,$A$4) Sorry about my earlier errors but hope this helps. "Toppers" wrote: Set up Corindex" in A5 to A8 with numbers 1,2 3, 4 =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&" *"),(Coreindex)),$A$1,$A$2,$A$3,$A$4),"N") I am sure there is a more elegant solution but this is my best shot! "Ted Horsch" wrote: Thanks very much for the quick reply. I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Topper,
I tried this last formula and it returns a #VALUE error. One thing: I had a typo below describing my spreadsheet. The rows are actuall as follows: C2 = ABCDCCCXYZ returns AAA C3 = ABCDEFGHIJK returns N C4 = ABCDEFGHDDD returns CCC C5 = AABBCCDDXYZ returns N I have A1 - A4 as follows AAA BBB CCC DDD And I added A5 - A8 as follows: 1 2 3 4 I put your formula in column D. Looks like this: Col A Col B Col C Col D AAA BBB ABCDCCCXYZ #VALUE! CCC ABCDEFGHIJK DDD ABCDEFGHDDD 1 AABBCCDDXYZ 2 3 4 Any other suggestions? "Toppers" wrote: Better .... =CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(C oreindex))+1,"N",$A$1,$A$2,$A$3,$A$4) Sorry about my earlier errors but hope this helps. "Toppers" wrote: Set up Corindex" in A5 to A8 with numbers 1,2 3, 4 =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&" *"),(Coreindex)),$A$1,$A$2,$A$3,$A$4),"N") I am sure there is a more elegant solution but this is my best shot! "Ted Horsch" wrote: Thanks very much for the quick reply. I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Ted,
I tried it with your data and it worked. Double check the formula below and if you still get problems, post w/sheet to me at toppers<atjohntopley.fsnet.co.uk in D": =CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(C oreindex))+1,"N",$A$1,$A$2,$A$3,$A$4) "Ted Horsch" wrote: Topper, I tried this last formula and it returns a #VALUE error. One thing: I had a typo below describing my spreadsheet. The rows are actuall as follows: C2 = ABCDCCCXYZ returns AAA C3 = ABCDEFGHIJK returns N C4 = ABCDEFGHDDD returns CCC C5 = AABBCCDDXYZ returns N I have A1 - A4 as follows AAA BBB CCC DDD And I added A5 - A8 as follows: 1 2 3 4 I put your formula in column D. Looks like this: Col A Col B Col C Col D AAA BBB ABCDCCCXYZ #VALUE! CCC ABCDEFGHIJK DDD ABCDEFGHDDD 1 AABBCCDDXYZ 2 3 4 Any other suggestions? "Toppers" wrote: Better .... =CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(C oreindex))+1,"N",$A$1,$A$2,$A$3,$A$4) Sorry about my earlier errors but hope this helps. "Toppers" wrote: Set up Corindex" in A5 to A8 with numbers 1,2 3, 4 =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&" *"),(Coreindex)),$A$1,$A$2,$A$3,$A$4),"N") I am sure there is a more elegant solution but this is my best shot! "Ted Horsch" wrote: Thanks very much for the quick reply. I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula using array to find embedded value
Ted,
Thanks for the feedback. I hope it works in the "real" world. "Ted Horsch" wrote: Toppers, My mistake, and a silly one. I corrected it and now it works as you say. You've been very generous with your time -- I am much obliged. Now I'll see if I can incorporate this into my real-world spreadsheet. Thanks a million! "Toppers" wrote: Ted, I tried it with your data and it worked. Double check the formula below and if you still get problems, post w/sheet to me at toppers<atjohntopley.fsnet.co.uk in D": =CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(C oreindex))+1,"N",$A$1,$A$2,$A$3,$A$4) "Ted Horsch" wrote: Topper, I tried this last formula and it returns a #VALUE error. One thing: I had a typo below describing my spreadsheet. The rows are actuall as follows: C2 = ABCDCCCXYZ returns AAA C3 = ABCDEFGHIJK returns N C4 = ABCDEFGHDDD returns CCC C5 = AABBCCDDXYZ returns N I have A1 - A4 as follows AAA BBB CCC DDD And I added A5 - A8 as follows: 1 2 3 4 I put your formula in column D. Looks like this: Col A Col B Col C Col D AAA BBB ABCDCCCXYZ #VALUE! CCC ABCDEFGHIJK DDD ABCDEFGHDDD 1 AABBCCDDXYZ 2 3 4 Any other suggestions? "Toppers" wrote: Better .... =CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(C oreindex))+1,"N",$A$1,$A$2,$A$3,$A$4) Sorry about my earlier errors but hope this helps. "Toppers" wrote: Set up Corindex" in A5 to A8 with numbers 1,2 3, 4 =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*"),(Corei ndex)),CHOOSE(SUMPRODUCT(COUNTIF(C2,"*"&Corename&" *"),(Coreindex)),$A$1,$A$2,$A$3,$A$4),"N") I am sure there is a more elegant solution but this is my best shot! "Ted Horsch" wrote: Thanks very much for the quick reply. I tried this solution and it is close but not exactly what I need. When I replace the "Y" with Corename I get the following results: C2 = ABCDCCCXYZ returns AAA C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns CCC C2 = AABBCCDDXYZ returns N Actually, the spreadsheet is set up with multiple rows: C2 = ABCDCCCXYZ returns AAA D2 = ABCDEFGHIJK returns N E2 = ABCDEFGHDDD returns CCC F2 = AABBCCDDXYZ returns N The range is in Col A rows 1 - 4. How can I get ABCDCCCXYZ to return CCC and ABCDEFGHDDD to return DDD? "Toppers" wrote: Replace the "Y" (including quotes) with 'Corename' =IF(SUMPRODUCT(COUNTIF(C2,"*"&Corename&"*")),Coren ame,"N") HTH "Ted Horsch" wrote: Im using the following formula to determine if a set of values (CoreNames) are present anywhere in cell C2: =IF(SUMPRODUCT(COUNTIF(C2,"*"&CoreName&"*")),"Y"," N") For example, if CoreName is a range containing four entries CoreName AAA BBB CCC DDD And my spread sheet contains these values in Col C Ill get the following results C2 = ABCDCCCXYZ returns Y C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns Y C2 = AABBCCDDXYZ returns N This works perfectly for me as is. However, I now need to go a step further and return the specific CoreName value instead of returning Y. For example, I want the following results from the values in Col C: C2 = ABCDCCCXYZ returns CCC C2 = ABCDEFGHIJK returns N C2 = ABCDEFGHDDD returns DDD C2 = AABBCCDDXYZ returns N Can this be done with an enhancement to my formula above? Excel 2003 SP2 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula with AND formula | Excel Discussion (Misc queries) | |||
| Array Formula to find Average Return | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Linear Mode
