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]

Have you put code in module?
Could u share the screenshot of cell with UDF?

Yes, I've put the code in module and have double checked that it's there.

 

[bebo021999]

Nope. My UDF is CPcount(...)
(mean Common Product Count)

 

[camspy]

Nope. My UDF is CPcount(...)
(mean Common Product Count)

View attachment 55354

Ah yes, sorry, I missed this.
It works like a charm now, thank you very much!

 

[kvsrinivasamurthy]

This is the result I got for the UDF.

 

[kvsrinivasamurthy]

In continuation to post14 these are the Attributes tally order

Apple	packaging,quantity,reliability,size,design,material,id,content	Grapes,Lychee,Durian,Grapefruit,Nectarine,Orange	5,5,4,4,4,4
Apricot	make,content,brand,purpose,feel,color,id,photos,weight,packaging	Grapefruit,Orange,Banana,Durian,Fig,Lime	8,5,4,4,4,4
Avocado	reliability,size,design,taste,smell,feel	Lychee,Durian,Apple,Banana,Lemon,Grapes	5,4,3,3,3,2
Banana	quantity,design,safety,brand,smell,id,feel,photos	Apricot,Durian,Orange,Peach,Apple,Avocado	4,4,4,4,3,3
Durian	size,color,content,feel,smell,quantity,brand,price,reliability	Grapes,Lychee,Plum,Apple,Apricot,Avocado	5,5,5,4,4,4
Fig	packaging,safety,weight,id,videos,photos	Grapefruit,Apricot,Orange,Banana,Lemon,Mango	5,4,4,3,3,3
Grapefruit	weight,purpose,photos,packaging,material,make,color,content,videos,taste	Apricot,Lime,Lychee,Mango,Nectarine,Orange	7,5,5,5,5,5
Grapes	quantity,reliability,size,make,material,purpose,content,price	Lychee,Plum,Apple,Durian,Nectarine,Grapefruit	6,6,5,5,5,4
Lemon	packaging,reliability,design,weight,smell,photos	Orange,Apple,Apricot,Avocado,Banana,Fig	4,3,3,3,3,3
Lime	packaging,quantity,weight,make,taste,material,price,feel	Grapefruit,Lychee,Watermelon,Apricot,Grapes,Apple	5,5,5,4,4,3
Lychee	weight,content,taste,reliability,design,material,size,price,purpose,feel	Grapes,Apple,Avocado,Durian,Grapefruit,Lime	6,5,5,5,5,5
Mango	taste,material,purpose,feel,videos,photos	Grapefruit,Lychee,Apricot,Lime,Orange,Pineapple	5,4,3,3,3,3
Nectarine	reliability,size,safety,make,material,content,color,photos	Plum,Grapefruit,Grapes,Apple,Apricot,Durian	7,5,5,4,4,4
Orange	packaging,quantity,reliability,make,purpose,smell,videos,photos	Grapefruit,Apricot,Grapes,Lemon,Apple,Banana	5,4,4,4,3,3
Papaya	size,design,weight,purpose,id,content	Lychee,Apple,Apricot,Grapefruit,Grapes,Avocado	5,4,4,4,3,2
Peach	design,safety,content,price,feel,photos,price	Pineapple,Lychee,Banana,Durian,Pear,Plum	6,5,4,4,4,4
Pear	packaging,design,safety,taste,purpose,price	Lychee,Grapefruit,Lime,Peach,Pineapple,Watermelon	4,3,3,3,3,3
Pineapple	safety,taste,content,price,feel,photos	Peach,Lychee,Apricot,Banana,Durian,Grapefruit	5,4,3,3,3,3
Plum	reliability,size,make,material,content,color,price,photos	Nectarine,Grapes,Durian,Grapefruit,Lychee,Apple	7,6,5,5,5,4
Watermelon	packaging,quantity,safety,brand,make,taste,material,content	Grapefruit,Lime,Apple,Apricot,Grapes,Nectarine	5,5,4,4,4,4

 

[camspy]

Thanks, you're the best! )

 

[camspy]

Hi @kvsrinivasamurthy!
I recently came up with a situation, when I needed to use your solution from this thread together with negative list.

So in short, I wanted to add one more column to the table, with a list of products that can't get listed as similar ones for the product from the same row.
Like if Apple can't have Grapes, Lemon go as similar products.

Is it possible to do?

 

[kvsrinivasamurthy]

Hi @kvsrinivasamurthy!
I recently came up with a situation, when I needed to use your solution from this thread together with negative list.

So in short, I wanted to add one more column to the table, with a list of products that can't get listed as similar ones for the product from the same row.
Like if Apple can't have Grapes, go as similar products.

Is it possible to do?

Hi @kvsrinivasamurthy!
I recently came up with a situation, when I needed to use your solution from this thread together with negative list.

So in short, I wanted to add one more column to the table, with a list of products that can't get listed as similar ones for the product from the same row.
Like if Apple can't have Grapes, Lemon go as similar products.

Is it possible to do?

I was on long break because of personal problem. I will look into it tomorrow.

 

[camspy]

Yes sure, no hurry

I was on long break because of personal problem. I will look into it tomorrow.

 

[kvsrinivasamurthy]

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