Identifying arrays that don't contain any defined values

TheAssetMgr

Board Regular
Joined
Nov 8, 2011
Messages
62
I have a column with values that look like: C-18,C-27,C-33,C-21 or C-21,C-25. The number of C-x values separated by a commas varies from row to row within the column.

What I need to do is drop a formula in an adjacent column that looks at the array in the first column to see if it contains a C-32 or C-33 or C-34 or C-35. If the array contains NONE of those values then I want to see "Error" in the formula column.

Thoughts?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
=IF(OR(ISERR(FIND("C-32",L31))=FALSE,ISERR(FIND("C-33",L31))=FALSE,ISERR(FIND("C-34",L31))=FALSE,ISERR(FIND("C-35",L31))=FALSE),"","Error")

Change L31 to your array.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
Give this formula a try...

=IF(SUM(0+ISNUMBER(FIND({"C-32","C-33","C-34","C-35"},A1)))>0,"","Error")

Note: Since you did not say, I chose to output nothing ("") if the cell contained one or more of the indicated values.
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
How about?

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"C-32,","C-33,","C-34,","C-35,"},A1&",")))),"","Error")
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609

ADVERTISEMENT

I don't like using excel formulas because it takes too long figuring out how to get them to work how I want. It's annoying. I find it much faster to make my own code. So here it goes.
Code:
Function myFunction(myCell)
     Application.Volitile
     mySplit = SPLIT(myCell, ", ")
     For Each myValue in mySplit
          If myValue = "C-32" OR _
               myValue = "C-33" OR _
               myValue = "C-34" OR _
               myValue = "C-35" Then
                    output = TRUE
            End If
       Next myValue
            If output <> TRUE Then
                 output = "ERROR"
            End If
     myFunction = output
End Function
I'm going to assume that your C-x values are in column A. Once you entered the above code into your vba macros, enter this formula into cell B2
B2 = myFunction(A2)
 
Last edited:

TheAssetMgr

Board Regular
Joined
Nov 8, 2011
Messages
62

ADVERTISEMENT

The massively high level of badassery on this board never ceases to amaze me. Rick that worked like a charm right out of the gate - thank you!

Andrew - I tried Rick's first since it was a few characters shorter and since it worked I'm going to charge on ahead. Thanks for the quick response though!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
Indeed, but unlike yours it also works for eg C-18,C-27,C-334,C-21.
@Andrew

Ah, the commas... I forgot about them! :oops:


@TheAssetMgr

As Andrew pointed out in Message #8, my formula, as written, could fail if you had three or more digits after the dash. Here is my corrected formula (I also removed the unnecessary >0 from my formula (Andrew's formula reminded me that is was unnecessary as well)...

=IF(SUM(0+ISNUMBER(FIND({"C-32,","C-33,","C-34,","C-35,"},A1&",")))>0,"","Error")

If you look carefully, you will see if we change the SUMPRODUCT to SUM (as mentioned in prior messages) and note the remaining differences are Andrew's "--" instead of my "0+" (two different ways to accomplish the same thing... in this case, to convert Boolean values to real numbers so they can be added, but the same "trick" can be used on text that looks like numbers in order to convert them from text to real numbers) and Andrew's use of SEARCH (allows the search to be case insensitive) as opposed to my use of FIND (which makes the search case sensitive), our formulas are identically constructed. The real difference between them whether your "C" characters will always be upper case or not, so use whichever applies to your circumstance.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,336
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top