how to show related words as drop down in another column in excel.

%3CLINGO-SUB%20id%3D%22lingo-sub-1440039%22%20slang%3D%22en-US%22%3Ehow%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440039%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20few%20sentences%20as%20a%20list%20and%20I%20need%20to%20search%20the%20sentence%20with%20one%20word%20in%20another%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20more%20details%20in%20the%20attachment.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1440039%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440407%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605555%22%20target%3D%22_blank%22%3E%40shekharreddy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attach%20a%20possible%20solution%20for%20this%20problem.%20It%20relies%20on%20splitting%20up%20the%20list%20into%203%20(Apple%2CNokia%2CSamsung)%20and%20using%20the%20NAMES%20feature.%20If%20you%20have%20other%20suppliers%20you%20can%20just%20create%20a%20new%20name%20for%20that%20supplier.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Epeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440439%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605555%22%20target%3D%22_blank%22%3E%40shekharreddy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20several%20ways%20to%20interpret%20what%20you've%20given%20us%2C%20so%20if%20what%20I've%20attached%20isn't%20what%20you%20were%20looking%20for%2C%20I%20need%20to%20ask%20for%20further%20clarification.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20solution%20uses%20the%20new%20Dynamic%20Array%20functions--notably%20FILTER%20and%20SORT%2C%20along%20with%20INDIRECT%20in%20the%20Data%20Validation%20screen--to%20generate%20dynamic%20lists%20of%20acceptable%20answers%20from%20the%20blue%20and%20white%20table%2C%20which%20can%20be%20expanded%20with%20the%20three%20manufactures%20listed.%20If%20you%20wanted%20to%20enlarge%20it%20to%20HP%20or%20Google%20you'd%20have%20to%20add%20more%20tables%20of%20%22second%20level%22%20acceptable%20answers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440455%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20came%20up%20with%20two%20overlapping%20but%20distinct%20solutions%20to%20this%20one%20too%2C%20Peter.%20Fun%20to%20meet%20this%20way.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440496%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20John%20-%20everyday%20is%20a%20school%20day!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442004%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442004%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20for%20your%20quick%20response.%20Actually%20I%20might%20not%20explain%20you%20well%20what%20exactly%20I%20am%20looking%20for.%20The%20given%20replay%20is%20perfect%20for%20few%20words.%20I%20work%20with%20large%20data.%20There%20were%20around%201M%2B%20records%20as%20words%20and%20sentences..In%20that%20case%20which%20formulae%20or%20code%20useful%20to%20me.%3C%2FP%3E%3CP%3EI'm%20sorry%20for%20the%20trouble%20and%20thank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442339%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605555%22%20target%3D%22_blank%22%3E%40shekharreddy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI'm%20sorry%20but%20I%20am%20not%20aware%20of%20any%20formula%20that%20will%20allow%20you%20to%20do%20that.%3C%2FP%3E%3CP%3EI%20think%20you%20can%20do%20this%20with%20VBA%20(Excel's%20programming%20language)%20but%20this%20will%20take%20some%20thought.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442830%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20show%20related%20words%20as%20drop%20down%20in%20another%20column%20in%20excel.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605555%22%20target%3D%22_blank%22%3E%40shekharreddy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EActually%20I%20might%20not%20explain%20you%20well%20what%20exactly%20I%20am%20looking%20for.%20The%20given%20replay%20is%20perfect%20for%20few%20words.%20I%20work%20with%20large%20data.%20There%20were%20around%201M%2B%20records%20as%20words%20and%20sentences.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20then%20you%20can%20give%20us%20a%20more%20complete%20description.%20Your%20first%20post%20said%20you%20worked%20with%20words%20and%20%22sentences%22%20but%20in%20fact%20the%20example%20included%20only%20short%20phrases%2C%20not%20sentences.%20It%20looked%20as%20if%20you%20might%20just%20be%20picking%20out%20%22sentences%22%20or%20%22phrases%22%20that%20referred%20to%20specific%20manufacturers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20now%20sounding%20as%20if%20that%20wasn't%20at%20all%20representative%20of%20your%20reality.%20If%20you%20could%20produce%20a%20better%20example%2C%20that%20might%20lead%20to%20a%20solution.%20It%20also%20might%20not%2C%20but%20at%20least%20we'd%20be%20working%20with%20your%20reality.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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. 

 

 

7 Replies
Highlighted

@shekharreddy 

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

 

 

@shekharreddy 

 

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.

Highlighted

@peteryac60 

 

We came up with two overlapping but distinct solutions to this one too, Peter. Fun to meet this way.

Highlighted

@mathetes 

 

Yes John - everyday is a school day!!

Highlighted

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!

 

 

Highlighted

@shekharreddy 

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

Highlighted

@shekharreddy 

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.