Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text search within a string using formula
Hi everybody,
I have in column A (say A2:A100), strings containing names and dates concatinated by "-". I have extracted the names using DataText To Column. The parsed names have been filtered to have unique names and are kept in another column say column F ( say F2 :F15). The names are of variable length (say 4 to 6 characters). As there could be more than one row containing data relating to the same name but of different dates, I want to use the unique names in Col F by referencing them in a formula to extract all data available against them in Col B ( B2:B100). I want to use sumif or sumproduct. I am not clear how to reference them as the name lengths are not uniform. As the names are in Col F, I am not able to use wildcards. Can any one help ? I give below a sample of data below: A B F Dat-Esbee-09-13-2006-S 200 Esbee Dat-Harry-12-23-2005-S 120 Harry Dat-Esbee-02-15-2007-S 270 Pete Dat-Pete-05-13-2006-P 120 TiaN Dat-TiaN-04-30-2006-S 325 The total of B for each name in F has to be arrived at using a formula. Thanks in advance. -- esbee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text search within a string using formula
Read this line as the intent:
The total of B for each name in F has to be arrived at using a formula. With your list of unique names in F2:F15, as posted Put in G2: =SUMPRODUCT(--ISNUMBER(SEARCH(F2,A$2:A$100)),B$2:B$100) Copy down to G15. This should return the required results for each unique name. Replace SEARCH with FIND if you need it to be a stricter, case sensitive search. FIND is case sensitive, while SEARCH is not. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "esbee" wrote: Hi everybody, I have in column A (say A2:A100), strings containing names and dates concatinated by "-". I have extracted the names using DataText To Column. The parsed names have been filtered to have unique names and are kept in another column say column F ( say F2 :F15). The names are of variable length (say 4 to 6 characters). As there could be more than one row containing data relating to the same name but of different dates, I want to use the unique names in Col F by referencing them in a formula to extract all data available against them in Col B ( B2:B100). I want to use sumif or sumproduct. I am not clear how to reference them as the name lengths are not uniform. As the names are in Col F, I am not able to use wildcards. Can any one help ? I give below a sample of data below: A B F Dat-Esbee-09-13-2006-S 200 Esbee Dat-Harry-12-23-2005-S 120 Harry Dat-Esbee-02-15-2007-S 270 Pete Dat-Pete-05-13-2006-P 120 TiaN Dat-TiaN-04-30-2006-S 325 The total of B for each name in F has to be arrived at using a formula. Thanks in advance. -- esbee |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text search within a string using formula
I am not able to use wildcards
Is that by choice or directive or is it that you can't figure out how to use wildcards? Try this: =SUMIF(A$2:A$6,"*"&F2&"*",B$2:B$6) -- Biff Microsoft Excel MVP "esbee" wrote in message ... Hi everybody, I have in column A (say A2:A100), strings containing names and dates concatinated by "-". I have extracted the names using DataText To Column. The parsed names have been filtered to have unique names and are kept in another column say column F ( say F2 :F15). The names are of variable length (say 4 to 6 characters). As there could be more than one row containing data relating to the same name but of different dates, I want to use the unique names in Col F by referencing them in a formula to extract all data available against them in Col B ( B2:B100). I want to use sumif or sumproduct. I am not clear how to reference them as the name lengths are not uniform. As the names are in Col F, I am not able to use wildcards. Can any one help ? I give below a sample of data below: A B F Dat-Esbee-09-13-2006-S 200 Esbee Dat-Harry-12-23-2005-S 120 Harry Dat-Esbee-02-15-2007-S 270 Pete Dat-Pete-05-13-2006-P 120 TiaN Dat-TiaN-04-30-2006-S 325 The total of B for each name in F has to be arrived at using a formula. Thanks in advance. -- esbee |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text search within a string using formula
Mr.Max,
Many thanks ! It is working. I tried Search function, but got #Value# error. I left it like that thinking it will not work. I have now learnt how it should be used. Thanks once again. -- esbee "Max" wrote: Read this line as the intent: The total of B for each name in F has to be arrived at using a formula. With your list of unique names in F2:F15, as posted Put in G2: =SUMPRODUCT(--ISNUMBER(SEARCH(F2,A$2:A$100)),B$2:B$100) Copy down to G15. This should return the required results for each unique name. Replace SEARCH with FIND if you need it to be a stricter, case sensitive search. FIND is case sensitive, while SEARCH is not. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "esbee" wrote: Hi everybody, I have in column A (say A2:A100), strings containing names and dates concatinated by "-". I have extracted the names using DataText To Column. The parsed names have been filtered to have unique names and are kept in another column say column F ( say F2 :F15). The names are of variable length (say 4 to 6 characters). As there could be more than one row containing data relating to the same name but of different dates, I want to use the unique names in Col F by referencing them in a formula to extract all data available against them in Col B ( B2:B100). I want to use sumif or sumproduct. I am not clear how to reference them as the name lengths are not uniform. As the names are in Col F, I am not able to use wildcards. Can any one help ? I give below a sample of data below: A B F Dat-Esbee-09-13-2006-S 200 Esbee Dat-Harry-12-23-2005-S 120 Harry Dat-Esbee-02-15-2007-S 270 Pete Dat-Pete-05-13-2006-P 120 TiaN Dat-TiaN-04-30-2006-S 325 The total of B for each name in F has to be arrived at using a formula. Thanks in advance. -- esbee |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text search within a string using formula
Mr.Valko,
Many thanks ! I never thought it will be so simple. My statement about usage of wildcards only reflects my lack of knowledge about their usage. I was not knowing that the asterix should be enclosed separately in double quotes and there should be "&" to connect them with the cell reference. Thanks for this knowledge. I had tried using the cell reference like this: "*F2*" instead of "*"&F2&"*" as shown by you. Thank you once again. -- esbee "T. Valko" wrote: I am not able to use wildcards Is that by choice or directive or is it that you can't figure out how to use wildcards? Try this: =SUMIF(A$2:A$6,"*"&F2&"*",B$2:B$6) -- Biff Microsoft Excel MVP "esbee" wrote in message ... Hi everybody, I have in column A (say A2:A100), strings containing names and dates concatinated by "-". I have extracted the names using DataText To Column. The parsed names have been filtered to have unique names and are kept in another column say column F ( say F2 :F15). The names are of variable length (say 4 to 6 characters). As there could be more than one row containing data relating to the same name but of different dates, I want to use the unique names in Col F by referencing them in a formula to extract all data available against them in Col B ( B2:B100). I want to use sumif or sumproduct. I am not clear how to reference them as the name lengths are not uniform. As the names are in Col F, I am not able to use wildcards. Can any one help ? I give below a sample of data below: A B F Dat-Esbee-09-13-2006-S 200 Esbee Dat-Harry-12-23-2005-S 120 Harry Dat-Esbee-02-15-2007-S 270 Pete Dat-Pete-05-13-2006-P 120 TiaN Dat-TiaN-04-30-2006-S 325 The total of B for each name in F has to be arrived at using a formula. Thanks in advance. -- esbee |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text search within a string using formula
welcome. pl drop the "Mr.", but do press the "Yes" button <g
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "esbee" wrote: Mr.Max, Many thanks ! It is working. I tried Search function, but got #Value# error. I left it like that thinking it will not work. I have now learnt how it should be used. Thanks once again. -- esbee |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text search within a string using formula
You're welcome. Thanks for the feedback!
When using a cell reference: =SUMIF(A$2:A$6,"*"&F2&"*",B$2:B$6) When hardcoding the criteria: =SUMIF(A$2:A$6,"*Esbee*",B$2:B$6) -- Biff Microsoft Excel MVP "esbee" wrote in message ... Mr.Valko, Many thanks ! I never thought it will be so simple. My statement about usage of wildcards only reflects my lack of knowledge about their usage. I was not knowing that the asterix should be enclosed separately in double quotes and there should be "&" to connect them with the cell reference. Thanks for this knowledge. I had tried using the cell reference like this: "*F2*" instead of "*"&F2&"*" as shown by you. Thank you once again. -- esbee "T. Valko" wrote: I am not able to use wildcards Is that by choice or directive or is it that you can't figure out how to use wildcards? Try this: =SUMIF(A$2:A$6,"*"&F2&"*",B$2:B$6) -- Biff Microsoft Excel MVP "esbee" wrote in message ... Hi everybody, I have in column A (say A2:A100), strings containing names and dates concatinated by "-". I have extracted the names using DataText To Column. The parsed names have been filtered to have unique names and are kept in another column say column F ( say F2 :F15). The names are of variable length (say 4 to 6 characters). As there could be more than one row containing data relating to the same name but of different dates, I want to use the unique names in Col F by referencing them in a formula to extract all data available against them in Col B ( B2:B100). I want to use sumif or sumproduct. I am not clear how to reference them as the name lengths are not uniform. As the names are in Col F, I am not able to use wildcards. Can any one help ? I give below a sample of data below: A B F Dat-Esbee-09-13-2006-S 200 Esbee Dat-Harry-12-23-2005-S 120 Harry Dat-Esbee-02-15-2007-S 270 Pete Dat-Pete-05-13-2006-P 120 TiaN Dat-TiaN-04-30-2006-S 325 The total of B for each name in F has to be arrived at using a formula. Thanks in advance. -- esbee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
search for date in long string of text | Excel Discussion (Misc queries) | |||
How do I do a multiple search using key words in a text string | Excel Discussion (Misc queries) | |||
How do I search a text string in many excel worksheets (files)? | Excel Worksheet Functions | |||
Newbie: How to search a text string from right | Excel Worksheet Functions |