Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb 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. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •