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

[HeatherF]

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.

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

 

[Eric W]

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
	A	B	C	D	E	F
1	John	Dwight	Susan	Anne	Denise
2	4699822	5472913	5836122	5836122	5990850
3	8146785	10010247	10022033	8014459	8182593
4	10080692	6769672	8258972	10030357	10082729
5	8258972	10022033	5962050	10010365	5836122
6	10030357	8378012	10023928	8080575	8051165
7	8326692	8080575	8146785	7765723
8		1009219	10008216	6769672
9		8173322	10024742
10		10024875	8182593
11		10008216
12		6052640
13
14
15		John	Dwight	Susan	Anne	Denise
16	John	6	0	2	1	0
17	Dwight	0	11	2	2	0
18	Susan	2	2	9	1	2
19	Anne	1	2	1	7	1
20	Denise	0	0	2	1	5
Sheet1

Cell Formulas
Range	Formula
B16:F20	B16	=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.

 

[HeatherF]

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!

 

[Eric W]

Happy to help! Thanks for the feedback and come back any time!

 

[HeatherF]

Happy to help! Thanks for the feedback and come back any time!

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.

 

[HeatherF]

Covariance Maxtrix.xlsx
	A	B	C	D	E	F
1	JOHN	DWIGHT	SUSAN	ANNE	DENISE
2	4699822	5472913	5836122	5836122	5990850
3	8146785	10010247	10022033	8014459	8182593
4	10080692	6769672	8258972	10030357	10082729
5	8258972	10022033	5962050	10010365	5836122
6	10030357	8378012	10023928	8080575	8051165
7	8326692	8080575	8146785	7765723
8		10009219	10008216	6769672
9		8173322	10024742
10		10024875	8182593
11		10008216
12		6052640
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
Range	Formula
B16:F20	B16	=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))))

 

[HeatherF]

Hey, I managed to do that! It says it's a Value Not Available error.

 

[Eric W]

You've got it right! You just need to add the headers in B15:F15, and A16:A20.

 

[HeatherF]

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!

 

[Eric W]

You're welcome! ?