Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 12th 09, 12:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 57
Default Check for text in multiple cells

Hello,

I would like to check multiple cells for a name and, if the name appears in
any of them, print that name to the destination cell. For instance, columns
A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for
"Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each
name should only appear in one of the columns in a row, so if "Bill' is in
C1, it won't be in A, E, G, or I 1.

Can this be done?
Thanks
Tom

  #2   Report Post  
Old November 12th 09, 12:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,204
Default Check for text in multiple cells

Not elegant, but it will work:

=IF(IF(A1="Bill",1,0)+IF(C1="Bill",1,0)+IF(E1="Bil l",1,0)+IF(G1="Bill",1,0)+IF(I1="Bill",1,0)=1,"Jus t One Bill","")

of course you could substitute a cell address where you'd type in a name for
"Bill" in all of the formulas.

"tommcbrny" wrote:

Hello,

I would like to check multiple cells for a name and, if the name appears in
any of them, print that name to the destination cell. For instance, columns
A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for
"Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each
name should only appear in one of the columns in a row, so if "Bill' is in
C1, it won't be in A, E, G, or I 1.

Can this be done?
Thanks
Tom

  #3   Report Post  
Old November 12th 09, 02:26 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Check for text in multiple cells

You can try the below formula in cell K1

=IF(SUMPRODUCT((MOD(COLUMN(A1:I1),2)=1)*(A1:I1="Bi ll")),"Bill","")

If this post helps click Yes
---------------
Jacob Skaria


"tommcbrny" wrote:

Hello,

I would like to check multiple cells for a name and, if the name appears in
any of them, print that name to the destination cell. For instance, columns
A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for
"Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each
name should only appear in one of the columns in a row, so if "Bill' is in
C1, it won't be in A, E, G, or I 1.

Can this be done?
Thanks
Tom

  #4   Report Post  
Old November 12th 09, 03:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 57
Default Check for text in multiple cells

Works well, thank you. Is there a way to substitute a list of names for
"Bill" so that any name in the list "Names" will be printed if found in the
cells checked?

"Jacob Skaria" wrote:

You can try the below formula in cell K1

=IF(SUMPRODUCT((MOD(COLUMN(A1:I1),2)=1)*(A1:I1="Bi ll")),"Bill","")

If this post helps click Yes
---------------
Jacob Skaria


"tommcbrny" wrote:

Hello,

I would like to check multiple cells for a name and, if the name appears in
any of them, print that name to the destination cell. For instance, columns
A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for
"Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each
name should only appear in one of the columns in a row, so if "Bill' is in
C1, it won't be in A, E, G, or I 1.

Can this be done?
Thanks
Tom

  #5   Report Post  
Old November 12th 09, 03:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 57
Default Check for text in multiple cells

Definitely works, thank you. Can I sub in a list of names for "Bill", so
that any name in the list "Names" will be printed if contained in the cells
checked?

"JLatham" wrote:

Not elegant, but it will work:

=IF(IF(A1="Bill",1,0)+IF(C1="Bill",1,0)+IF(E1="Bil l",1,0)+IF(G1="Bill",1,0)+IF(I1="Bill",1,0)=1,"Jus t One Bill","")

of course you could substitute a cell address where you'd type in a name for
"Bill" in all of the formulas.

"tommcbrny" wrote:

Hello,

I would like to check multiple cells for a name and, if the name appears in
any of them, print that name to the destination cell. For instance, columns
A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for
"Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each
name should only appear in one of the columns in a row, so if "Bill' is in
C1, it won't be in A, E, G, or I 1.

Can this be done?
Thanks
Tom



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check text in range of cells tommcbrny Excel Discussion (Misc queries) 2 September 11th 09 05:11 PM
Check multiple cells for a match Rusty Excel Discussion (Misc queries) 4 May 15th 09 06:57 AM
link check boxes in multiple cells Taz22i Excel Discussion (Misc queries) 1 July 17th 08 04:24 PM
check a row ignore blank cells print only those with text Rollo Tomasi Excel Discussion (Misc queries) 4 May 18th 07 08:32 AM
How to check multiple cells Sharon Mann Excel Worksheet Functions 3 January 22nd 06 02:35 PM


All times are GMT +1. The time now is 06:35 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017