Compare and extract the words that matches

[ExcelNewbie2020]

sirs,

i need to compare the words in "Particular" column in sheet 1 against the "Particular" column of sheet 2.
Then, matched words will be extracted accordingly..
Comparison must be based with "Category". Please see below table for details

Name-List-for-Practice.xlsm
	A	B	C	D	E
1		SHEET1		SHEET 2
2	Category	Particulars	Expected result	Particulars	Category
3	a	small portal hub editors method resources tasks	small, method, small, method	Community portal – The central hub for editors, with resources, links, tasks, and announcements.	d
4	a	note method works best small descriptions	note, method, works, best, small, descriptions, note, method, works, best, small, descriptions	Village pump – Forum for discussions about Wikipedia itself, including policies and technical issues.	d
5	a	news Sources of news about broader	news, broader	Site news – Sources of news about Wikipedia and the broader Wikimedia movement.	d
6	b	Teahouse Ask basic questions editing Wikipedia.	questions	Teahouse Ask basic questions about using or editing Wikipedia.	c
7	b	Help desk about using editing Wikipedia.	desk, about	Help desk Ask questions about using or editing Wikipedia.	c
8	b	Reference desk Ask research questions encyclopedic	Reference, research, questions, encyclopedic	Reference desk Ask research questions about encyclopedic topics.	b
9	c	help Content unique teahouse way to navigate encyclopedia	Teahouse, help	Content portals – A unique way to navigate the encyclopedia.	b
10				Please note that this method works best for small descriptions	a
11				Please note that this method works best for small descriptions	a
12				news portal – sources central hub for editors with news broader tasks and announcements.	a
13
Sheet2

 

[kevin9999]

I'm not sure that I totally follow your logic. For example, in the first row you list 'small' and 'method' twice each (because they appear twice in the "a" 'Category' on sheet 2) - but you don't include 'portal', 'hub', 'editors' or 'tasks' which also appear in the "a" 'Category' on sheet 2? In row 6 you expect 'questions' but you don't expect 'Ask' which also appears in the "b" 'Category' on sheet 2?
Anyhow, the following code is a starting point which will no doubt be improved (by me or others) as we go along. Starting with the following layouts on sheets 1 & 2:

test.xlsm
	A	B	C
1	Category	Particulars	Expected result
2	a	small portal hub editors method resources tasks
3	a	note method works best small descriptions
4	a	news Sources of news about broader
5	b	Teahouse Ask basic questions editing Wikipedia.
6	b	Help desk about using editing Wikipedia.
7	b	Reference desk Ask research questions encyclopedic
8	c	help Content unique teahouse way to navigate encyclopedia
Sheet1

test.xlsm
	A	B
1	Particulars	Category
2	Community portal – The central hub for editors, with resources, links, tasks, and announcements.	d
3	Village pump – Forum for discussions about Wikipedia itself, including policies and technical issues.	d
4	Site news – Sources of news about Wikipedia and the broader Wikimedia movement.	d
5	Teahouse Ask basic questions about using or editing Wikipedia.	c
6	Help desk Ask questions about using or editing Wikipedia.	c
7	Reference desk Ask research questions about encyclopedic topics.	b
8	Content portals – A unique way to navigate the encyclopedia.	b
9	Please note that this method works best for small descriptions	a
10	Please note that this method works best for small descriptions	a
11	news portal – sources central hub for editors with news broader tasks and announcements.	a
Sheet2

The following code...

Option Explicit
Sub Test_Compare()
    Dim ws1 As Worksheet, ws2 As Worksheet, r As Range
    Set ws1 = Worksheets("Sheet1")          '<-- *** Change to actual sheet names ***
    Set ws2 = Worksheets("Sheet2")
    Dim a, b, c, d
    Dim i As Long, j As Long, k As Long
    Dim s1 As String, s2 As String, s3 As String
    
    Set r = ws1.Range("A2", ws1.Cells(Rows.Count, "B").End(xlUp))
    r.Value2 = Application.Trim(r)
    a = r
    b = ws2.Range("A2", ws2.Cells(Rows.Count, "B").End(xlUp))
    ReDim d(1 To UBound(a, 1), 1 To 1)
    
    For i = 1 To UBound(a, 1)
        s1 = a(i, 1)
        For j = 1 To UBound(b, 1)
            If b(j, 2) = s1 Then s2 = s2 & " " & b(j, 1)
        Next j
        c = Split(Trim(a(i, 2)))
        For k = LBound(c) To UBound(c)
            If InStr(1, s2, c(k), vbTextCompare) > 0 Then s3 = s3 & c(k) & ", "
        Next k
        s3 = Left(s3, Len(s3) - 2): d(i, 1) = s3: s3 = ""
    Next i

ws1.Range("c2").Resize(UBound(d, 1), 1).Value = d
End Sub

Produces this result...

test.xlsm
	A	B	C
1	Category	Particulars	Expected result
2	a	small portal hub editors method resources tasks	small, portal, hub, editors, method, tasks
3	a	note method works best small descriptions	note, method, works, best, small, descriptions
4	a	news Sources of news about broader	news, Sources, news, broader
5	b	Teahouse Ask basic questions editing Wikipedia.	Ask, questions
6	b	Help desk about using editing Wikipedia.	desk, about
7	b	Reference desk Ask research questions encyclopedic	Reference, desk, Ask, research, questions, encyclopedic
8	c	help Content unique teahouse way to navigate encyclopedia	help, Content, unique, teahouse, way, to, navigate, encyclopedia
Sheet1

 

[ExcelNewbie2020]

I'm not sure that I totally follow your logic. For example, in the first row you list 'small' and 'method' twice each (because they appear twice in the "a" 'Category' on sheet 2) - but you don't include 'portal', 'hub', 'editors' or 'tasks' which also appear in the "a" 'Category' on sheet 2? In row 6 you expect 'questions' but you don't expect 'Ask' which also appears in the "b" 'Category' on sheet 2?
Anyhow, the following code is a starting point which will no doubt be improved (by me or others) as we go along. Starting with the following layouts on sheets 1 & 2:

my bad..your are right.. i had missed those words.. the code works fine with category a and b except for category c.

thank you sir for the reply. really appreciate..

 

[kevin9999]

except for category c.

When I look at category c on sheet 1, and compare it with category c on sheet 2, it seems to me to be working correctly. What did you expect category c to return in the Expected result column (given that all the words that are being returned in the demo in post #2 are present in the category c section of sheet 2)?