How do I create Simularity/Correlation Matrix using Non-Numeric Data

HeatherF

New Member
Joined
Mar 3, 2020
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I am trying to create a matrix of constituents in our database comparing their Relationships to other constituents. I have a list of constituents, and each one has a list of people they have relationships with (represented here are constituent IDs for people attached to their record as a relation).
What I would like to do is the relationship list for every person in our database to every other person in our database, and identify people who share the highest number of relationships in common.
So here is a list of constituents, the ID#s of each person listed as a relationship (could just as easily be their names). Below I used the Correlation Analysis from the Data Analysis Toolpak.
Simularity matrix.jpg

John and Dwight have no one in common, so their score should be zero, right? I think the issue is that my numbers are not actually that, they're random ID#s representing a person. But I can't use the Correlation Matrix on non-numeric data.
Is there a canned analysis tool or downloadable spreadsheet with formulas that will do what I want?
Thanks so much,
Heather
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Welcome to the MrExcel forum!

I'd have to review my statistics, but as you've seen, the correlation matrix doesn't really get you what you want. Consider:

Book1
ABCDEF
1JohnDwightSusanAnneDenise
246998225472913583612258361225990850
38146785100102471002203380144598182593
410080692676967282589721003035710082729
58258972100220335962050100103655836122
61003035783780121002392880805758051165
78326692808057581467857765723
81009219100082166769672
9817332210024742
10100248758182593
1110008216
126052640
13
14
15JohnDwightSusanAnneDenise
16John60210
17Dwight011220
18Susan22912
19Anne12171
20Denise00215
Sheet1
Cell Formulas
RangeFormula
B16:F20B16=SUMPRODUCT(COUNTIF(INDEX($A$2:$E$13,0,MATCH($A16,$A$1:$E$1,0)),INDEX($A$2:$E$13,0,MATCH(B$15,$A$1:$E$1,0))))


This formula finds out how many numbers each person has in common. And you can use names instead of numbers. Maybe this will work for you.

Also, check out the XL2BB tool in my signature and in the response box. It allows you to insert a sample of your sheet that other people can easily copy/paste into their spreadsheets to test with. Many people won't bother trying to manually type in a lot of data to test with.

Let us know if this helps.
 
Upvote 0
Thank you! This is exactly what I was looking for! I truly appreciate it.
I’m sorry, I did not notice the XL2BB tool but I will definitely use that in the future.
Thanks again!
 
Upvote 0
Happy to help! Thanks for the feedback and come back any time! :cool:
Hi Eric,
I'm sorry to have to post again--I suspect this is a very simple thing but I'm not sure how to apply the formula. Solely in cell B16? In every cell in the new range? I'm either getting #N/A or the formula pasted over data in the original table.
 
Upvote 0
Covariance Maxtrix.xlsx
ABCDEF
1JOHNDWIGHTSUSANANNEDENISE
246998225472913583612258361225990850
38146785100102471002203380144598182593
410080692676967282589721003035710082729
58258972100220335962050100103655836122
61003035783780121002392880805758051165
78326692808057581467857765723
810009219100082166769672
9817332210024742
10100248758182593
1110008216
126052640
13
14
15
16#N/A#N/A#N/A#N/A#N/A
17#N/A#N/A#N/A#N/A#N/A
18#N/A#N/A#N/A#N/A#N/A
19#N/A#N/A#N/A#N/A#N/A
20#N/A#N/A#N/A#N/A#N/A
mini
Cell Formulas
RangeFormula
B16:F20B16=SUMPRODUCT(COUNTIF(INDEX($A$2:$E$13,0,MATCH($A16,$A$1:$E$1,0)),INDEX($A$2:$E$13,0,MATCH(B$15,$A$1:$E$1,0))))
 
Upvote 0
Hey, I managed to do that! It says it's a Value Not Available error.
 
Upvote 0
Eureka! Thank you! I hadn't thought about that because the Data Analysis Toolpak analysis do it automatically, but it works! you're a lifesaver!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top