Substring Search within an Array of Strings (PART 2)

[voryzover]

Everyone, I posted the following below yesterday and got help from Yogi and Asala42.
When I posted this, it didn't occur to me that I would want to locate the main string in which the substring is found.

So to summarize: Search for a string (say rang) inside an array of strings placed over a range (say {"apple","orange","lemon"} placed at A1:A3). The command should return the 1st string that it finds a match (in that case this would be "orange")

My yesterday's entry is right below and I also pasted the link to the related discussion.

And as always, thanks a lot.

------------------------------------------

Hi everyone

First let me express my grattitude for everyone taking their time to answer questions on
this board. At least personnally speakig, this board is extremely valuable.

On to question: I know this must be a piece of cake for the experts.

I wish to search for a substring within an array of strings and just return back an indication
that the substring exists withing that array.

so for instance let the substring be "rang"
and let the array be
{"apple","orange","lemon"}.
I wish to put a single command line next to my substring and wish to search through that
range and return a key code if it exists. And then I will repeat the same search for other
substrings. For the moment being, I don't care too much about the number of occurences
or the exact cell address where they occur. All I wish to know is whether they exist in that
list or not.

So it looks to me it is like a SEARCH command but except on a range of text (Rather than
a single text) with an array formula. I have tried MATCH but my case is not an exact match
either.

Any help will be greatly appreciated.

Thanks

http://www.mrexcel.com/board/viewtopic.php?topic=8889&forum=2

 

[Aladin Akyurek]

=INDEX(A1:A3,MATCH("*"&E1&"*",A1:A3,0))

 

[voryzover]

Thanks Aladin, this worked out.

 

[Simply]

I have an array with PARTS across the column headings and PRODUCTS down the first column. I have 100 potential parts, which can be "grouped" into categories like color. For example, "red door", "red wheel", "red window", etc. can be collectively found under "red...".
Under each column is the number of the parts needed to produce each corresponding product, and the parts can be used on more than one product.


Here's an example of what I'm trying to achieve. I'd like a formula in cell A1 of Sheet2, which would search the top row of Sheet1 for any parts that have "red" in them, and then search column A in Sheet1 for any products that have "car" in them, and sum the total count needed to produce one of each of these.

http://http://pictureexample.blogspot.com/

The results should be:
columns B, F, J, N are included.
rows 2, 3, 8 are included.

so... cells B2, B3, B8, F2, F3, F8, J2, J3, J8, N2, N3, N8 would be summed.
How can I write a one-cell formula which will sum a count of all of the parts needed to produce one of each of the "red" products?

 

[Aladin Akyurek]

I have an array with PARTS across the column headings and PRODUCTS down the first column. I have 100 potential parts, which can be "grouped" into categories like color. For example, "red door", "red wheel", "red window", etc. can be collectively found under "red...".
Under each column is the number of the parts needed to produce each corresponding product, and the parts can be used on more than one product.


Here's an example of what I'm trying to achieve. I'd like a formula in cell A1 of Sheet2, which would search the top row of Sheet1 for any parts that have "red" in them, and then search column A in Sheet1 for any products that have "car" in them, and sum the total count needed to produce one of each of these.

http://http://pictureexample.blogspot.com/

The results should be:
columns B, F, J, N are included.
rows 2, 3, 8 are included.

so... cells B2, B3, B8, F2, F3, F8, J2, J3, J8, N2, N3, N8 would be summed.
How can I write a one-cell formula which will sum a count of all of the parts needed to produce one of each of the "red" products?

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("car",$A$2:$A$8)),IF(ISNUMBER(SEARCH("red",$B$1:$N$1)),$B$2:$N$8)))

 

[Simply]

Thank you Aladin!!

I never knew about these "secret" class of formulas!

 

[Aladin Akyurek]

Thank you Aladin!!

I never knew about these "secret" class of formulas!

You are welcome. Thanks for providing feedback.

 

[Simply]

Aladin,

How would the formula need to be adjusted if I need to search for more than one string of characters in each cell? For example, if I needed to find the "red" AND "blue" parts for both the "trucks" AND "cars"?

Can the same formula be used?

 

[Aladin Akyurek]

Aladin,

How would the formula need to be adjusted if I need to search for more than one string of characters in each cell? For example, if I needed to find the "red" AND "blue" parts for both the "trucks" AND "cars"?

Can the same formula be used?

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("red",$B$1:$N$1))+
       ISNUMBER(SEARCH("blue",$B$1:$N$1)),
     IF(ISNUMBER(SEARCH("car",$A$2:$A$8))+
       ISNUMBER(SEARCH("truck",$A$2:$A$8)),$B$2:$N$8)))

 

[Simply]

Thank you again Aladin!

You have been most helpful to my project. I really appreciate it.