Jun 04 2020 04:43 AM
Hello,
I have few sentences as a list and I need to search the sentence with one word in another sheet.
Please find the more details in the attachment.
Jun 04 2020 07:09 AM
I attach a possible solution for this problem. It relies on splitting up the list into 3 (Apple,Nokia,Samsung) and using the NAMES feature. If you have other suppliers you can just create a new name for that supplier.
Hope this helps.
peter
Jun 04 2020 07:20 AM
There are several ways to interpret what you've given us, so if what I've attached isn't what you were looking for, I need to ask for further clarification.
This solution uses the new Dynamic Array functions--notably FILTER and SORT, along with INDIRECT in the Data Validation screen--to generate dynamic lists of acceptable answers from the blue and white table, which can be expanded with the three manufactures listed. If you wanted to enlarge it to HP or Google you'd have to add more tables of "second level" acceptable answers.
Jun 04 2020 07:23 AM
We came up with two overlapping but distinct solutions to this one too, Peter. Fun to meet this way.
Jun 04 2020 07:31 AM
Jun 04 2020 09:30 PM
Thank you so much for your quick response. Actually I might not explain you well what exactly I am looking for. The given replay is perfect for few words. I work with large data. There were around 1M+ records as words and sentences..In that case which formulae or code useful to me.
I'm sorry for the trouble and thank you in advance!
Jun 05 2020 01:38 AM
Hi
I'm sorry but I am not aware of any formula that will allow you to do that.
I think you can do this with VBA (Excel's programming language) but this will take some thought.
Good luck.
Peter
Jun 05 2020 06:01 AM
You wrote: Actually I might not explain you well what exactly I am looking for. The given replay is perfect for few words. I work with large data. There were around 1M+ records as words and sentences.
Maybe then you can give us a more complete description. Your first post said you worked with words and "sentences" but in fact the example included only short phrases, not sentences. It looked as if you might just be picking out "sentences" or "phrases" that referred to specific manufacturers.
It's now sounding as if that wasn't at all representative of your reality. If you could produce a better example, that might lead to a solution. It also might not, but at least we'd be working with your reality.