How do I group these objects into those that can be linked to each other?

Question

I have a list of items and matches that have been found, like this.

item	matches
apple	tree;window
box	microscope
chimney	dish;fence
dish
fence
headphones
microscope	box;headphones
painting
window	chimney;apple

I want to put them into groups, where every object that can be 'reached' by another object is in the same group as that object. So for example, apple is in the same group as fence because, in the 'matches' column, we can go apple -> window -> chimney -> fence.

However, apple is not in the same group as microscope because there is no path between the two. They don't list each other in the 'matches' column, and none of those items list the other item in its 'matches' column, and so on.

item	matches	group
apple	tree;window	1
box	microscope	2
chimney	dish;fence	1
dish		1
fence		1
headphones		2
microscope	box;headphones	2
painting		3
window	chimney;apple	1

^This is what the third column, group, should look like. Is there a way I can do this with a LAMBDA function, preferably without addins?

Something like TEXTJOIN will not work, as despite a 'path' existing, no string match will connect 'apple' and 'chimney' because a match doesn't exist.

Thanks.

Answer 1

Give this a try:

=XMATCH("*"&A2&"*",REDUCE(,A$1:A10,LAMBDA(m,n,IF(COUNT(FIND(n,m)),m,VSTACK(m,REDUCE(n,A2:A10,LAMBDA(x,y,TEXTJOIN(";",,x,XLOOKUP(TEXTSPLIT(x,";"),A:A,B:B,"")))))))),2)-1