+ Reply to Thread
Results 1 to 7 of 7

sumproduct formula improvements for matchmaking

  1. #1
    Registered User
    Join Date
    06-08-2020
    Location
    Vienna
    MS-Off Ver
    Office365
    Posts
    4

    sumproduct formula improvements for matchmaking

    Hello,

    I am working on a matchmaking excel sheet to match different members of our community with another, based on similar interests, goals & industries. In order to match them, I used a sum product formula: =SUMPRODUCT(--($B12:$BK12=$B2:$BK2)*($B12:$BK12<>"")). The outcome is a matrix with two axes that shows the number of exact matches in their answers.

    For now, if I add people, I have to manually change the formula. In order to "automatise" the matchmaking process as much as possible, I would like to change the formula in a way so that I can just drag it over to new people added, probably by adding "$" at the right positions. So far, I haven't managed to place them correctly. I would really appreciate your help, also happy about other suggestions on how to improve my matchmaking sheet.

    Thanks,

    Maria
    Attached Files Attached Files
    Last edited by Maria_sky; 06-22-2020 at 09:50 AM. Reason: attachment added

  2. #2
    Forum Contributor
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2013
    Posts
    132

    Re: sumproduct formula improvements for matchmaking

    If I understand what you are trying to do. Try this in cell BL12 and then copy across.

    =SUMPRODUCT(--($B12:$BK12=INDIRECT("$B"&MATCH(BL$11,$A$1:$A8,0)&":$BK"&MATCH(BL$11,$A$1:$A8,0)))*($B12:$BK12<>""))

    The range in the MATCH portion will have to increase as you add more names down the A column. I could use your services. Too bad you're in another country.


    EDIT: On second thought MATCH matches the first occurrence of the match so you could have a much larger range without having to change it. i. e.
    =SUMPRODUCT(--($B12:$BK12=INDIRECT("$B"&MATCH(BL$11,$A$1:$A1000,0)&":$BK"&MATCH(BL$11,$A$1:$A1000,0)))*($B12:$BK12<>""))

    Probably could even do the whole column.

    =SUMPRODUCT(--($B12:$BK12=INDIRECT("$B"&MATCH(BL$11,$A:$A,0)&":$BK"&MATCH(BL$11,$A:$A,0)))*($B12:$BK12<>""))
    Last edited by bird333; 06-22-2020 at 11:43 AM.

  3. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: sumproduct formula improvements for matchmaking

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by BMV; 06-22-2020 at 03:50 PM.

  4. #4
    Registered User
    Join Date
    06-08-2020
    Location
    Vienna
    MS-Off Ver
    Office365
    Posts
    4

    Re: sumproduct formula improvements for matchmaking

    Hello bird333,

    thank you so much for your help! unfortunately there is some error in the formula you suggested and I cannot figure it out! Could you maybe apply the formula in the excel sheet I uploaded to see if it works, and then upload the excel sheet again? Very thankful for your input.

    Best, Maria

  5. #5
    Registered User
    Join Date
    06-08-2020
    Location
    Vienna
    MS-Off Ver
    Office365
    Posts
    4

    Re: sumproduct formula improvements for matchmaking

    Hi BMV, thank you for your help! Unfortunately the formula did not work when I tried it. Could you maybe apply it in the excel sheet and upload it again? thank you so much, Maria

  6. #6
    Forum Contributor
    Join Date
    06-12-2020
    Location
    USA
    MS-Off Ver
    2013
    Posts
    132

    Re: sumproduct formula improvements for matchmaking

    Quote Originally Posted by Maria_sky View Post
    Hello bird333,

    thank you so much for your help! unfortunately there is some error in the formula you suggested and I cannot figure it out! Could you maybe apply the formula in the excel sheet I uploaded to see if it works, and then upload the excel sheet again? Very thankful for your input.

    Best, Maria
    I put the second version in the yellow cell.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-08-2020
    Location
    Vienna
    MS-Off Ver
    Office365
    Posts
    4

    Re: sumproduct formula improvements for matchmaking

    This is amazing, thank you so much for your help!

    Resolved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Weighted Matchmaking in Excel
    By PL17M in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2020, 12:56 PM
  2. Highlighting improvements within excel
    By doritos5991 in forum Excel General
    Replies: 1
    Last Post: 09-07-2019, 08:10 PM
  3. If statements and VBA improvements
    By gelen4o in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2016, 09:29 AM
  4. Insert row if efficency improvements
    By cossie2k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2014, 03:59 AM
  5. Improvements to ExcelForum.com
    By Phoenix5794 in forum Suggestions for Improvement
    Replies: 1
    Last Post: 07-12-2012, 11:02 AM
  6. [SOLVED] Performance Improvements while using RTD
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-19-2005, 08:15 AM
  7. Message Box Improvements
    By Phil Hageman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1