SOLVED

Greetings, I hope you are well. I just need some formula help.

Highlighted
Occasional Contributor

I have several cells in a row that have Y or N in them, I want to enter a formula in a column for when one of those cells in a row is "Y" to return a "Y" value.

Also I have in those cells returning a "Y" value if say "Steel" or "Copper" is in a product name in column A. I wondered how to create a formula that gives a "Y" value if a cell name contains "Steel" or "Copper" or "Nickel".

Thirdly, how can I return a "Y" value for a product name in column A having "Ni" or "Cu" in the name on its own without returning a false positive for say "Continiti" or "Occupose" etc. but for say "Product C Ni Cu Plate"

Many Thanks

18 Replies
Highlighted

HI @UKBlueSteel 

 

It would be great if you can attach sample file along with your desired output enter manually 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

Highlighted

@UKBlueSteel 

As variants

1) for columns from A to C

=IF(SUMPRODUCT(--(A1:C1="Y")),"Y","N")

 2) For A1

=IF(SUM(COUNTIF(A1,"*"&{"Steel","Cooper","Nickel"}&"*")),"Y","N")

3) same as above if only take " Ni " and " Cu ". If spaces around is not the case, when it shall be another formal logic.

 

Highlighted
Thank you so much, I was googling and searching and trying for a while to do this, this really helped me a lot.

I was using =IF(IS NUMBER(SEARCH("Nickel",A2)),"Y","N")

It wasn't as good as your formulas which solved the problems I was having.

Really appreciate the help and the interest from others in the community. Thank you Sergei

Don
Highlighted
Best Response confirmed by UKBlueSteel (Occasional Contributor)
Solution

@UKBlueSteel ,,,

 

  • For Row has Y & N:
=IF(COUNTIF($A$2:$E$2,"Y")>0,"Y","N")

Or to make the formula dyanamic,, you can use this even:

=IF(COUNTIF($A$2:$E$2,A1)>0,"Y","N")

Where A1 has Y, or you may put other alphaet to test.
  • To check the products:
=IF(OR(A1={"Steel","Cooper","Nickel"}),"Y","N")

For Ni and others:

=IF(OR(A1={"St","Co","Ni"}),"Y","N")
Highlighted

@UKBlueSteel ,,,

 

Let me point out the mistake in the formula you have used,

 

=IF(IS NUMBER(SEARCH("Nickel",A2)),"Y","N")

 

Space is not allowed between IS & Number,,, since ISNUMBER is reserve word or a Function.

So use it as,,

 

=IF(ISNUMBER(SEARCH("Nickel",A2)),"Y","N") , will works.

 

Not let me say about combination of ISNUMBER & SEARCH.

 

  • ISNUMBER returns TRUE for numbers, and FALSE for anything else.
  • SEARCH finds the text/substring , and returns it's position as a number.
  • Where ISNUMBER returns TRUE, if finds text at the position.
  •  If SEARCH doesn't finds the text/substring, returns a #VALUE! error, which causes ISNUMBER to return FALSE.

 

Note: In your case since formula tests more than one product at a time, so looks useless, but if you alter the formula will work.

 

=IF(ISNUMBER(SEARCH(E1,A1)),"Y","N")

 

  • Where E1 has either Nickel or Copper or Steel (use drop down in cell E1 to make it more effective).
  • You can replace SEARCH with FIND, but remember, SEARCH is case-insensitive, while FIND is case-sensitive. 
Highlighted
Very interesting, thanks Rajesh. I learnt a lot from your post, very invaluable information.

I did wonder about case and non case sensitive functions.
Highlighted
Glad to help you,,, in case you are satisfied with my post then you may mark it as best answer/post. Keep asking ☺
Highlighted

Hi Rajesh@Rajesh-S 

I was wondering if I had several columns with different metals in that has "Y" in, say

Columns F2-I2 and I wanted a formulat for column E that gives a Y if there is just one Y in any of those columns but if there is a N in column J2 it gives a "N" that trumps any "Y" value?

Thank You

Highlighted

@UKBlueSteel ,,,

 

I'm  sure that you are looking for this:

 

=IF($J2="N","N",IF(COUNTIF($F2:$I2,"Y")=1,"Y","No Match"))

 

You may adjust cell references in the formula as needed.

Highlighted
Glad to help you,, keep asking ☺
Highlighted
Thank you so much for this! I wonder do you know where I could learn more about these advanced functions? Like an online course or to get MS Excel certification? Thank you Rajesh
Highlighted
Find any near by reputed School/Institute, conducting Course on MS-Excel basic to advance, or Professional extending his/her support, better go for class room teaching or man 2 man interaction. ☺

Highlighted
Thanks for your advice, are you sure you don't mind me keep asking things?

So column A is a list of materials, Pipe, wrench, plastic rod, etc.

Column B says whether they are metallic or not with others that haven't been classified stating "No Match"

How can I find the most commonly material name that relates to column B "No Match" values only? Is it possible to get a list of first, second and third most common?
Highlighted
It's nice one to attempt please share some sample data along with expected output, will help me & others to test the applied method!!
Highlighted
Greetings Rajesh,

I hope you are well, I solved the previous question using:

{=INDEX(A2:A5121,MODE(IF(D2:D5121=P18, MATCH (A2:A5121,0))))}

With P18 = No Match

Also I'm just having a bit of toggle transferring a formula so

=IF(SUM(COUNTIF(A2,"*"&P12&"*")),"N","Y")

With P 12 being "Iron", "Copper", "Steel" etc.
Also using the same formulae with a list say instead of P12 I use P26:P75 with a list of metals like

Nickel
Copper
Iron
Etc.

Would these formulas need some modifying?

Many thanks

Don

Iron
Nickel
Coppse formulate
Highlighted

@UKBlueSteel 

 

You need an array (CSE) formula :

 

Rajesh-S_0-1602244033547.png

 

  • Formula in cell G10

 

 

{=IF(SUM(COUNTIF($G$5:$G$8,"*"&G4&"*")),"Y","N")}

Or you can use this one too:

{=IF(SUM(COUNTIF($G$5:$G$8,G4)),"Y","N")}

Because "*"&G4&"*" works as partial match, (wild card).

 

 

 

N.B. 

  • Enter formula without { } ,,  but finish with Ctrl+Shift+Enter.
  • If you enter any name is in list (G5:G8), you get Y.
  • You may extend this formula to check if any name is more than 1.

 

 

{=IF(SUM(COUNTIF($G$5:$G$8,"*"&G4&"*"))>1,"Y","N")}

 

 

  • Or you may use non array formula even:

=IF((COUNTIF($G$5:$G$8,G4)),"Y","N")

 

Adjust cell references in the formula as needed.

 

Highlighted
Hi Rajesh,

I hope you are well, I can't get the formulas to work, maybe I've missed something.

So I have cell values from A2 onwards to A1000, with each cell having text values like steel bolt, or wood box etc.

Then I have in ref P2:P250 non metallic identifiers in a column so wood, plastic, glass etc. Then Q2:Q250 metallic identifiers so steel, iron, brass etc.

I would like a formula that says Y if it recognises a metallic identifier or N if not and then vice versa for non metallic.

The previous formulas even with pressing crtl alt and enter fine give the same values as the previous formulas with many identifiers in the formula?

Many thanks for any help.
Highlighted
Could to please share/upload the Workbook, you are working with,, help me & others to fix the issue in faster way.