+ Reply to Thread
Results 1 to 7 of 7

Generate a "similarity" matrix on non-continuous values (not numbers)?

  1. #1
    Registered User
    Join Date
    03-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    44

    Question Generate a "similarity" matrix on non-continuous values (not numbers)?

    Dear all,

    I have this matrix of non-continuous, independent data (let's call them "a", "b", "c")

    Please Login or Register  to view this content.
    Values are not continous numbers, or measures, but more of a label for each "Variable". Additionally, "a" in Variable 1 does not relate to "a" in Variable 2.

    I would like a way to assess similarity (=shared values) between "Samples". I don't care to know which "Variable" is similar or different between the two "Samples", just the number of shared values is fine.

    For the example above, we see that:
    - Sample 1 has 2 shared value with Sample 3 (for Variable 2)
    - Sample 2 has 0 shared value with Sample 1 for any variable
    - Sample 3 has 0 shared values with Sample 2
    - Each sample has 3 shared values with itself

    In that example, Sample 1 and 3 are more similar to each other than Sample 2 (if we exclude self-similarity).

    I guess a good way of outputing this is to create a "similarity" matrix:

    Shared values:

    Please Login or Register  to view this content.
    Does it sound like something possible to do in Excel?

    Thanks a lot for any help. I hope I was clear enough!

    All the best,

    G.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Generate a "similarity" matrix on non-continuous values (not numbers)?

    Try this where your data table (including headers is in H1:K5) and your comparision table is in A1:E5

    In B2
    =SUM(1*(INDEX($I$2:$K$5,MATCH(B$1,$H$2:$H$5,0),)=INDEX($I$2:$K$5,MATCH($A2,$H$2:$H$5,0),))) entered as an array (CNTRL SHFT ENTER instead of ENTER)
    Drag across and down

    See attachment
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Generate a "similarity" matrix on non-continuous values (not numbers)?

    It works perfectly, thanks a lot!

  4. #4
    Registered User
    Join Date
    07-09-2013
    Location
    France
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Generate a "similarity" matrix on non-continuous values (not numbers)?

    Thanks for the solution indeed !

    I am now struggling to adapt/expand the formula provided by ChemistB using a condition -> something like: "if the two cells that are compared are empty, don't count them as similar"...

    See the objective in attachment: ComparisonTable_v2.xlsx

    Any help would be much appreciated

    Cheers,

    Y.

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Generate a "similarity" matrix on non-continuous values (not numbers)?

    yomlao,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  6. #6
    Registered User
    Join Date
    03-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Generate a "similarity" matrix on non-continuous values (not numbers)?

    Dear all,

    I have previously solved my initial problem using the answer above, but I have the same related question as #4, so I post it here.

    The formula provided in #2 works perfectly with the exception that empty cells are considered to be identical. As the poster in #4, I am also struggling to adapt the formula to not take into account the empty cells and would be grateful for some help in this.

    Thank you very much for your help and your time.
    G.

  7. #7
    Registered User
    Join Date
    03-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Generate a "similarity" matrix on non-continuous values (not numbers)?

    (Just marked the thread as unsolved, as I was advised to post the new related question below it)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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