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

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

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

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

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

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

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