Matrix matching function

[markjohns]

I created an x by y non-symmetric matrix in which, for values 1-5, "O" denotes a "preference to match". A "match" occurs only if both values reciprocate the preference (1 and 2 are a "match" because both prefer the other, while 1 and 3 are NOT a match because the preference isn't reciprocated).

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"].[/TD]
[TD="align: center"].[/TD]
[TD="align: center"].[/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"].[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"].[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"].[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NULL[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]y[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]NULL[/TD]
[/TR]
</tbody>[/TABLE]

1 and 2 match
1 and 4 match
1 and 5 match
2 and 5 match
3 and 5 match
4 and 5 match
1 and 3 do NOT match
2 and 3 do NOT match
2 and 4 do NOT match
3 and 4 do NOT match

First question - Is there a "match" function that lists the unique 1x1 set of matches?
Second question - Is there an "iterative match" function that lists the unique set of matches after 'n' iterations?

For example, since 1 and 4 both match with 5, then I would like to group 1, 4, and 5. I'd like the function to apply to an x by y matrix and have matches for 1 by 1, 1 by 1 by 1, 1 by 1 by 1 by ... z iterations.

 

[Eric W]

Welcome to the forum.

Interesting question. Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	.	.	x			Matches
2	1	NULL	O	-	O	O	O						01&02
3	2	O	NULL	O	O	O	-						01&04
4	3	O	-	NULL	-	O	-						01&05
5	4	O	-	O	NULL	O	O						02&05
6	5	O	O	O	O	NULL	O						03&05
7	.						NULL						04&05
8	.							NULL
9	.								NULL
10	y									NULL
11
Sheet3

Cell Formulas
Range	Formula
M2	{=IFERROR(TEXT(SMALL(IFERROR(IF(ROW($B$2:$G$6)$B$2:$G$6),IF($B$2:$G$6=TRANSPOSE($B$2:$G$6),(ROW($B$2:$G$6)-ROW($B$2)+1)*100+COLUMN($B$2:$G$6)-COLUMN($B$2)+1)),""),ROWS($M$2:$M2)),"00&00"),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

This seems to work with your example. However, it might be better accomplished with VBA. And your second question seems like it would absolutely require VBA. I'm not exactly sure of your requirements, but it seems like you'd like to take a subset of the rows and columns based on the results of the matches, create a smaller array then repeat until you hit some stopping condition.

 

[markjohns]

That works really well. Very neat.

[TABLE="width: 544"]
<colgroup><col span="12"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Null[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]O[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]01&02[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]O[/TD]
[TD]Null[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]01&04[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]Null[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]01&05[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]Null[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]01&07[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]Null[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]01&08[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]Null[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]02&04[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]Null[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]02&05[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]Null[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]02&06[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]Null[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]02&07[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD] [/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD]Null[/TD]
[TD] [/TD]
[TD]02&08[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]03&05[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]03&06[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]03&07[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]03&09[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]04&05[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]04&09[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]04&10[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]05&06[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]05&07[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]05&08[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]05&10[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]06&09[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]07&09[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]08&09

[/TD]
[/TR]
</tbody>[/TABLE]
Thank you. I'll post another question regarding the VBA. Really appreciate it. Cheers!

 

[Eric W]

Consider the last match of your example 8 & 9. These are matching 2 spaces, not 2 Os. Is that what you wanted, or did you only want to match Os? Also, a caveat. The table must start on A1, or B2, or C3, or D4, etc. I can also fix the formula to handle other starting cells if that's an issue.

 

[markjohns]

Ah, yes I only wanted to match Os. I think I know how to correctly edit the formula, though. Thank you.