Two Part Problem with IF and Substitute

[Excell1677]

Hello Excellers,

I've encounter some issues dealing with a spreadsheet that I've been working on...I enclosed the spreadsheet within this post...

In tab titled "Problem 1" I'm seeking a method to switch symbols with text into another symbol (i.e if text has # then change to ?) I've tried using an If with Substitute formula but it would just return an error. I even tried using a table to check the text against but still received errors. The second issue is on the tab titled "Problem 2" I have a bunch of words in which I would like to check those against a list and if the word doesn't have any of the words from the list then create it. I've used an Index-Match function but it didn't return the desired results. I've enclosed a spreadsheet that has both this problems any help on one or both of these problems would be greatly appreciated.

Thank you for your time

[AlKey]

Enter formula in F3 and copy down

Please Login or Register  to view this content.

[FlameRetired]

@ AlKey

I'm surprised to find that the ISNUMBER wrapped around FIND is necessary. (I made that change to my formula, and it did the trick.)

I would think that any non-zero number returned by FIND would be sufficient for a TRUE test. Clearly it's not. Can you tell us why this is?

Thanks,
Dave

[Excell1677]

Thanks AlKey this solution worked perfectly!

[bebo021999]

So problem #1 has been solved.

For problem #2, try this array formula in F2:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Drag down as far as needed.

[Excell1677]

Hello bebo021999,

Thank You for taking time to solve this, and yes the 1st problem has been solved. I tried you solution and it worked but when I used two words instead of one then it doesn't work. For example when the word "apple" is used the formula works great but if I used "apple recipes" then an error occurs..I enclosed a file of this example

[bebo021999]

O I got it. Let define:
K column: list of children
B column: mixture of parent and children
what are parent items: those are strings have no children at the end.
For example:
Apple: Parent, but
Apple Sugar: Children
Apple baking: Parent
Cabbage receipe: Parent, but
Cabbage receipe Sauce: Chilren

Do you agree with this?

[bebo021999]

This is what I got so far with formula solution, I think a helper column is needed (column A) to identify the parent items.
But with VBA solution, it is faster and helper unneeded and tell me if you need.

[Excell1677]

I think but let me provide an another example so we're both on the same page..

For Example:
List 1:
Orange
Apple Sugar
Kiwi Salt
Grapes Pepper
Grape Cooking

List 2:
Sugar
Salt

The goal would be (list 3): Append words from List 1 to List 2 if none of the words are present in list 1
List 3
Orange Sugar
Orange Salt
Grapes Pepper Salt
Grapes Pepper sugar
Grapes Cooking Sugar
Grapes Cooking Salt

[Excell1677]

Hello @ bebo021999

Thank you for taking the time to find a solution for this problem after experimenting with your solution (the one that included the helper column) I found that it worked very well for what I needed...If you do happen across this message I am interested into how you would go about creating a vba for this...

Thanks again..