Advanced Lookup with Keyword Grouping

[Pete2020]

I am reposting this from Ozgrid - Did not got a solution. I abide the forum rules.

Looking for VBA Macro solution or a VB Function

Problem Overview

• Sheet 2 Column A should Match with Sheet 1 of Column A - Exact Match (Primary Key)
• Sheet 2 Column B and C at row level should match against each cell of column B in Sheet 1
• Column B - single cell may have multiple words - All words should exist at cell level match in sheet 1 column B
• Column C - words should not contain at row level - It can have multiple words

Search rules

• keywords consider exact space match as mentioned in sheet 2 column B" chat bot" or "Chat bot"
• Comparison should be at individual Word level and Not within a word. ( Targeting and Retargeting are separate- [How ever Re Targeting is same as targeting]
• Match should not be case sensitive
• Match with Plural words for the each Keyword such as “Es” and"s" ( Row 10 and 11 of sheet 1)
• If None of the Keywords matched it should display as Not found
• Sheet 1 Comparison should be done at each cell level aganist each row at column A sheet 1

Out Put

• - All the matched pairs should be populated in Column C and Column D of sheet 1 with comma delimiter
• - If multiple words within a cell are matched - then should add "-" with matched pairs (Only Column B words and NOT Column C words)
• - All the adjacent rows From Column D to L of sheet 2 be populated to sheet 1 with "|" delimiter separated (Only Populate Unique Words with ";"delimiter)

Sheet 2 -

Search and Match Sample data.xlsx
	A	B	C	D	E
1	Keyword1-exact Match- Column A of sheet 1	Keyword2-Word Match- within the Cell	Keyword-Do not Contain	Primay Category	Sub Category words
2	Facebook	Retargeting	Complete	Social Media	Facebook Retargeting Course-without complete
3	Facebook	Complete,Book		Social Media	Complete Book on Facebook
4	Facebook	Chatbot, chat bot		Social Media	Facebook Chatbot Training
5	Facebook	Analytic		Social Media	Facebook Analytics Course
6	Facebook	Funnel		Social Media	Facebook Funnel Course
7	Facebook	Marketing		Social Media	Facebook Marketing Course
8	Facebook	Marketing,Pixel		Social Media	Facebook Pixel Marketing Course
9	Facebook	Group		Social Media	Facebook Group Course
10	Facebook	Ecommerce		Social Media	Facebook Ecommerce Course
11	Facebook	Messenger		Social Media	Facebook Messenger Course
12	Facebook	Strategies		Social Media	Facebook Strategies Course
13	Facebook	Advertising	ultimate,course	Social Media	Facebook Advertising Course
14	Facebook	Funnel		Social Media	Facebook Funnels Course
15	Facebook	Ads	Business	Social Media	Facebook Ads Course
16	Facebook	Advanced		Social Media	Facebook Advanced Course
17	Facebook	Page		Social Media	Facebook Page Course
18	Twitter	Marketing,Guide		Social Media	Twitter Course
19	Twitter	Celebrity		Social Media	Twitter Course
20	Twitter	Endorsement		Social Media	Twitter Course
21	Twitter	Sales		Social Media	Twitter Course
22	Twitter	Monetize		Social Media	Twitter Course
23	Twitter	Quora		Social Media	Twitter Course
24	Twitter	Affiliate	Marketing	Social Media	Twitter Course
25	Twitter	Follower		Social Media	Twitter Course
26	Twitter	Brand		Social Media	Twitter Course
27	Twitter	Minute		Social Media	Twitter Course
28	Twitter	Ultimate		Social Media	Twitter Course
Sheet2

 

[Pete2020]

Sheet 1 data - The expected Output in Column C, D and E from sheet2 above

Search and Match Sample data.xlsx
	A	B	C	D	E
1	Primary Category	Sheet2 -Partial match from - Check with Sheet Column B and C on sheet 2	Keyword/Lookup Values- SHEET 2 Colum B and C - At each Cell level more than 2 words matched it should add seperator "-" at column and rest of the word Comma seperated	Primary	Once Keyword match, the Column E to L of Sheet 2 to be Populated by "|" Delimiter seperated based on Keyword match - Only Unique Values
2	Facebook	Facebook Marketing Build Facebook Messenger Chat bots	chat bot,messenger,Marketing	Social Media	Facebook Chatbot Course|Facebook Messenger Course|Facebook Marketing Course
3	Facebook	Facebook Page Masterclass: Use It To Grow Your Business	Page	Social Media	Facebook Page Course
4	Facebook	The Facebook Retargeting Course	Retargeting	Social Media	Facebook Retargeting Course-without complete
5	Facebook	The Complete Facebook Retargeting Course	Not found
6	Facebook	Facebook Chatbot Marketing: The Smart Way To Use Manychat	Chatbot,Marketing	Social Media	Facebook Chatbot Course|Facebook Marketing Course
7	Facebook	Facebook Pixel Marketing Course: The Smart Way To Use Manychat	Pixel-marketing, Marketing	Social Media	Facebook Pixel Marketing | Facebook Marketing Course
8	Facebook	Growing A Profitable Facebook Group	Group	Social Media	Facebook Group Course
9	Facebook	Facebook Marketing: Complete Book on Advanced Targeting Strategies	Complete-book,Advanced,Strategies	Social Media	Complete Book on Facebook|Facebook Advanced Course|Facebook Strategies Course
10	Facebook	Facebook Ads For Local Service Businesses	Not found	Social Media
11	Facebook	The Basics Of Facebook Analytics	Analytic	Social Media	Facebook Analytics Course
12	Facebook	Easy To Create Facebook Ad Funnels That Convert	Funnel	Social Media	Facebook Funnels Course
13	Facebook	The Ultimate Facebook Advertising Course	Not found	Social Media
14	Facebook	Facebook Ads For Ecommerce: Guide To Selling Any Product	Ecommerce,Ads	Social Media	Facebook Ecommerce Course|Facebook Ads Course
15	Facebook	Facebook Algorithms	Not found
16	Twitter	Complete Twitter Marketing For Business Growth And Followers	Follower	Social Media	Twitter Course
17	Twitter	Twitter Affiliate Marketing Or Get Sales On Autopilot	Sales	Social Media	Twitter Course
18	Twitter	Twitter Marketing: Gain Huge Following And Monetize Twitter	Monetize	Social Media	Twitter Course
19	Twitter	Celebrity Twitter: Build Your Twitter With Endorsements	Celebrity,Endorsements	Social Media	Twitter Course
20	Twitter	7 Steps To Building Your Brand Using Twitter	Brand	Social Media	Twitter Course
21	Twitter	Affiliate Training Course: Get Sales With Quora And Twitter	Quora,Affiliate	Social Media	Twitter Course
22	Twitter	Twitter Marketing Secrets A Stepbystep Complete Guide	Guide	Social Media	Twitter Course
23	Twitter	Rock Social Media In 30 Minutes A Day Twitter, Facebook+	Minute	Social Media	Twitter Course
24	Twitter	Ultimate Twitter Marketing Guide	Marketing-Guide,Ultimate	Social Media	Twitter Course
Sheet1

 

[Pete2020]

I have around 65k records and i am finding difficulty in using advanced formulas.

Is there any VBA solution which can be faster.

 

[Pete2020]

If there is any chance of Excel formula either through Vlookup or Match and Index is also fine.