ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Matching Two Text Strings to see if Like (https://www.excelbanter.com/excel-worksheet-functions/124018-matching-two-text-strings-see-if-like.html)

caldog

Matching Two Text Strings to see if Like
 
Hope this will be my last time to have to ask a question of you experts.

I have an individual name that I am trying to see if it is in a particular
column, if it is then just put a one, or what ever number of times it
appears. Sometimes there are two columns that I need to check if the name
appears, and then just add up all of the occurrences.

Example:

A B C D E
1. T. Bodie J. Lang None
2. T. O'Connell T. Bodie T. Spurgeon
3. M. Lalonde N. Martz
4. T. Spurgeon B. Geisler J. Lang
5. S. Slonina T. O'Connell B. Hodge

So what I want to do is see how many times say a J. Lang appears. And put
this number in a different spot on my spreadsheet.

Any help,

Thanks

Steve


Martin Fishlock

Matching Two Text Strings to see if Like
 
Hi,

Try using countif(A:A,"J. Lang")


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"caldog" wrote:

Hope this will be my last time to have to ask a question of you experts.

I have an individual name that I am trying to see if it is in a particular
column, if it is then just put a one, or what ever number of times it
appears. Sometimes there are two columns that I need to check if the name
appears, and then just add up all of the occurrences.

Example:

A B C D E
1. T. Bodie J. Lang None
2. T. O'Connell T. Bodie T. Spurgeon
3. M. Lalonde N. Martz
4. T. Spurgeon B. Geisler J. Lang
5. S. Slonina T. O'Connell B. Hodge

So what I want to do is see how many times say a J. Lang appears. And put
this number in a different spot on my spreadsheet.

Any help,

Thanks

Steve


Max

Matching Two Text Strings to see if Like
 
Another thought ..

Assuming the source range is A1:E5

Try in say, F1:
=SUMPRODUCT(--ISNUMBER(MATCH(A1:E5,{"J. Lang"},0)))

Or, pointing to an input cell for the name, say in G1: J. Lang
=SUMPRODUCT(--ISNUMBER(MATCH(A1:E5,G1,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"caldog" wrote:
Hope this will be my last time to have to ask a question of you experts.

I have an individual name that I am trying to see if it is in a particular
column, if it is then just put a one, or what ever number of times it
appears. Sometimes there are two columns that I need to check if the name
appears, and then just add up all of the occurrences.

Example:

A B C D E
1. T. Bodie J. Lang None
2. T. O'Connell T. Bodie T. Spurgeon
3. M. Lalonde N. Martz
4. T. Spurgeon B. Geisler J. Lang
5. S. Slonina T. O'Connell B. Hodge

So what I want to do is see how many times say a J. Lang appears. And put
this number in a different spot on my spreadsheet.

Any help,

Thanks

Steve


caldog

Matching Two Text Strings to see if Like
 
Again as usual you guys come through.

thanks works just great.

"Max" wrote:

Another thought ..

Assuming the source range is A1:E5

Try in say, F1:
=SUMPRODUCT(--ISNUMBER(MATCH(A1:E5,{"J. Lang"},0)))

Or, pointing to an input cell for the name, say in G1: J. Lang
=SUMPRODUCT(--ISNUMBER(MATCH(A1:E5,G1,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"caldog" wrote:
Hope this will be my last time to have to ask a question of you experts.

I have an individual name that I am trying to see if it is in a particular
column, if it is then just put a one, or what ever number of times it
appears. Sometimes there are two columns that I need to check if the name
appears, and then just add up all of the occurrences.

Example:

A B C D E
1. T. Bodie J. Lang None
2. T. O'Connell T. Bodie T. Spurgeon
3. M. Lalonde N. Martz
4. T. Spurgeon B. Geisler J. Lang
5. S. Slonina T. O'Connell B. Hodge

So what I want to do is see how many times say a J. Lang appears. And put
this number in a different spot on my spreadsheet.

Any help,

Thanks

Steve


Max

Matching Two Text Strings to see if Like
 
"caldog" wrote:
Again as usual you guys come through.
thanks works just great.


Good to hear that, Steve ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---




All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com