# Thread: Matrix matching function Thanks: 0 Likes: 0

1. ## Matrix matching function

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).

 1 2 3 4 5 . . . x 1 NULL O - O O 2 O NULL O O O 3 O - NULL - O 4 O - O NULL O 5 O O O O NULL . NULL . NULL . NULL y NULL

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.

2. ## Re: Matrix matching function

Welcome to the forum.

Interesting question. Try:

ABCDEFGHIJKLM
1123456..xMatches
21NULLO-OOO01&02
32ONULLOOO-01&04
43O-NULL-O-01&05
54O-ONULLOO02&05
65OOOONULLO03&05
7.NULL04&05
8.NULL
9.NULL
10yNULL
11

Sheet3

Array Formulas
CellFormula
M2{=IFERROR(TEXT(SMALL(IFERROR(IF(ROW(\$B\$2:\$G\$6)<COLUMN(\$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"),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

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.

3. ## Re: Matrix matching function

That works really well. Very neat.

 1 2 3 4 5 6 7 8 9 10 1 Null O O O O 01&02 2 O Null O O O O O O O O 01&04 3 O Null O O O O O 01&05 4 O O O Null O O O O 01&07 5 O O O O Null O O O O 01&08 6 O O O O Null O O 02&04 7 O O O Null O O O 02&05 8 O O O O O Null 02&06 9 O O O O O Null 02&07 10 O O O O O O O Null 02&08 03&05 03&06 03&07 03&09 04&05 04&09 04&10 05&06 05&07 05&08 05&10 06&09 07&09 08&09
Thank you. I'll post another question regarding the VBA. Really appreciate it. Cheers!

4. ## Re: Matrix matching function

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.

5. ## Re: Matrix matching function

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