Generate related lists of products that have similar attributes

[camspy]

We have a list of products in column A.
Each product has its set of attributes in column B.

The task is to generate a product with its 6 related products (in column C), which have similar attributes to that product, sorted by similarity.

Similar attributes mean that the products share a similar set of attributes. The more matching attributes, the more similar the product is.

Then, the most similar product goes to the top of the list, and the least similar product goes to the bottom.

The result should exclude the product for which we generated the list.

I provide sample data below.
In C2, I have put an example of what the result should look like.

sort.xlsx
	A	B	C
1	Product	Atributes	Result: 6 related products
2	Apple	packaging,quantity,reliability,size,design,material,id,content	Pear,Orange,Lemon,Fig,Lime,Lychee
3	Apricot	make,content,brand,purpose,feel,color,id,photos,weight,packaging
4	Avocado	reliability,size,design,taste,smell,feel
5	Banana	quantity,design,safety,brand,smell,id,feel,photos
6	Durian	size,color,content,feel,smell,quantity,brand,price,reliability
7	Fig	packaging,safety,weight,id,videos,photos
8	Grapefruit	weight,purpose,photos,packaging,material,make,color,content,videos,taste
9	Grapes	quantity,reliability,size,make,material,purpose,content,price
10	Lemon	packaging,reliability,design,weight,smell,photos
11	Lime	packaging,quantity,weight,make,taste,material,price,feel
12	Lychee	weight,content,taste,reliability,design,material,size,price,purpose,feel
13	Mango	taste,material,purpose,feel,videos,photos
14	Nectarine	reliability,size,safety,make,material,content,color,photos
15	Orange	packaging,quantity,reliability,make,purpose,smell,videos,photos
16	Papaya	size,design,weight,purpose,id,content
17	Peach	design,safety,content,price,feel,photos,price
18	Pear	packaging,design,safety,taste,purpose,price
19	Pineapple	safety,taste,content,price,feel,photos
20	Plum	reliability,size,make,material,content,color,price,photos
21	Watermelon	packaging,quantity,safety,brand,make,taste,material,content
Sheet1

Thanks in advance.

 

[camspy]

As i am not clear please give example of expected result.

Here it is.
So, imagine, that without the negatives in the 1st row, Pear would have been added to the results column.
But since Pear is in the negatives list, it wasn't added. Instead, the next appropriate related product was added (Durian).

Book1
	A	B	C	D
1	Product	Atributes	Negatives	Result: 6 related products
2	Apple	packaging,quantity,reliability,size,design,material,id,content	Pear,Banana	Orange,Lemon,Fig,Lime,Lychee,Durian
3	Apricot	make,content,brand,purpose,feel,color,id,photos,weight,packaging
4	Avocado	reliability,size,design,taste,smell,feel
5	Banana	quantity,design,safety,brand,smell,id,feel,photos
6	Durian	size,color,content,feel,smell,quantity,brand,price,reliability
7	Fig	packaging,safety,weight,id,videos,photos
8	Grapefruit	weight,purpose,photos,packaging,material,make,color,content,videos,taste
9	Grapes	quantity,reliability,size,make,material,purpose,content,price
10	Lemon	packaging,reliability,design,weight,smell,photos
11	Lime	packaging,quantity,weight,make,taste,material,price,feel
12	Lychee	weight,content,taste,reliability,design,material,size,price,purpose,feel
13	Mango	taste,material,purpose,feel,videos,photos
14	Nectarine	reliability,size,safety,make,material,content,color,photos
15	Orange	packaging,quantity,reliability,make,purpose,smell,videos,photos
16	Papaya	size,design,weight,purpose,id,content
17	Peach	design,safety,content,price,feel,photos,price
18	Pear	packaging,design,safety,taste,purpose,price
19	Pineapple	safety,taste,content,price,feel,photos
20	Plum	reliability,size,make,material,content,color,price,photos
21	Watermelon	packaging,quantity,safety,brand,make,taste,material,content
Sheet1

 

[kvsrinivasamurthy]

What do you mean by negative list. Where it is located.

 

[camspy]

What do you mean by negative list. Where it is located.

It's located in the col C.
I will get them added to column C, some rows can have 1 negative, some can have multiple ones comma delimited, and some can have no negatives.
Negatives are simply exclusions - the items that shouldn't be added to the final list of related products. But at the same time, there should still be 6 related products.
Here's some image that could help you understand how the Exclusion list (negatives) could look like:

 

[kvsrinivasamurthy]

Try this code

Function GetRelatedProductsLessNegative(RngPrd As Range, AtriRng As Range, CriPrd As String, CriAtb As String, NegCri As String)
Dim M, N, temp
Dim Ta As Long, T As Long, K As Long, Cnt As Long, X As Long
M = Split(CriAtb, ",")

Cnt = AtriRng.Cells.Count
ReDim Tly(1 To Cnt) As Long
With CreateObject("scripting.dictionary")

For Ta = 1 To Cnt
For T = 0 To UBound(M)
Tly(Ta) = Tly(Ta) + Evaluate("1*ISnumber(FIND(""" & M(T) & """," & AtriRng.Cells(Ta, 1).Address & "))")
Next T
.Add Tly(Ta) * 1000 - Ta, RngPrd.Cells(Ta, 1)
Next Ta

For Ta = 1 To Cnt
K = Application.Large(.keys, Ta)
If K > 0 And LCase(.Item(K)) <> LCase(CriPrd) Then
If InStr(1, LCase(NegCri), LCase(.Item(K))) > 0 Then GoTo Line1
temp = temp & "," & .Item(K)
X = X + 1
If X > 6 Then Exit For
End If
Line1:
Next Ta
End With
If temp = "" Then GetRelatedProducts = "" Else GetRelatedProductsLessNegative = Mid(temp, 2)
End Function

In D2 then copied down.

=GetRelatedProductsLessNegative($A$2:$A$21,$B$2:$B$21,A2,B2,C2)

Product	Atributes	Negatives	Result: 6 related products
Apple	packaging,quantity,reliability,size,design,material,id,content	Durian,orange	Grapes,Lychee,Grapefruit,Nectarine,Papaya,Plum,Watermelon
Apricot	make,content,brand,purpose,feel,color,id,photos,weight,packaging		Grapefruit,Orange,Banana,Durian,Fig,Lime,Lychee
Avocado	reliability,size,design,taste,smell,feel		Lychee,Durian,Apple,Banana,Lemon,Grapes,Lime
Banana	quantity,design,safety,brand,smell,id,feel,photos	Apricot,Apple	Durian,Orange,Peach,Avocado,Fig,Lemon,Mango
Durian	size,color,content,feel,smell,quantity,brand,price,reliability		Grapes,Lychee,Plum,Apple,Apricot,Avocado,Banana
Fig	packaging,safety,weight,id,videos,photos		Grapefruit,Apricot,Orange,Banana,Lemon,Mango,Apple
Grapefruit	weight,purpose,photos,packaging,material,make,color,content,videos,taste		Apricot,Lime,Lychee,Mango,Nectarine,Orange,Plum
Grapes	quantity,reliability,size,make,material,purpose,content,price		Lychee,Plum,Apple,Durian,Nectarine,Grapefruit,Lime
Lemon	packaging,reliability,design,weight,smell,photos	Banana	Orange,Apple,Apricot,Avocado,Fig,Grapefruit,Lychee
Lime	packaging,quantity,weight,make,taste,material,price,feel		Grapefruit,Lychee,Watermelon,Apricot,Grapes,Apple,Durian
Lychee	weight,content,taste,reliability,design,material,size,price,purpose,feel		Grapes,Apple,Avocado,Durian,Grapefruit,Lime,Papaya
Mango	taste,material,purpose,feel,videos,photos		Grapefruit,Lychee,Apricot,Lime,Orange,Pineapple,Avocado
Nectarine	reliability,size,safety,make,material,content,color,photos		Plum,Grapefruit,Grapes,Apple,Apricot,Durian,Lychee
Orange	packaging,quantity,reliability,make,purpose,smell,videos,photos		Grapefruit,Apricot,Grapes,Lemon,Apple,Banana,Durian
Papaya	size,design,weight,purpose,id,content		Lychee,Apple,Apricot,Grapefruit,Grapes,Avocado,Banana
Peach	design,safety,content,price,feel,photos,price		Pineapple,Lychee,Banana,Durian,Pear,Plum,Apricot
Pear	packaging,design,safety,taste,purpose,price		Lychee,Grapefruit,Lime,Peach,Pineapple,Watermelon,Apple
Pineapple	safety,taste,content,price,feel,photos		Peach,Lychee,Apricot,Banana,Durian,Grapefruit,Lime
Plum	reliability,size,make,material,content,color,price,photos		Nectarine,Grapes,Durian,Grapefruit,Lychee,Apple,Apricot
Watermelon	packaging,quantity,safety,brand,make,taste,material,content	Grapefruit,Lime,Apple,Apricot,Grapes,Nectarine	Banana,Durian,Lychee,Orange,Pear,Pineapple,Plum

 

[camspy]

Try this code

It brings 7 results, not 6

 

[kvsrinivasamurthy]

Change this line

If X > 6 Then Exit For

as

If X > 5 Then Exit For

 

[camspy]

Thank you!
It seems to work fine