Identifying arrays that don't contain any defined values

TheAssetMgr

Board Regular
Joined
Nov 8, 2011
Messages
63
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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top