Cross-referencing a large set of categories pair by pair

[crackinthekraken]

Hey guys, let's say you had to compare a very large group of categories, each with a set of defined traits. The goal is to compare every category against every other category and identify the pairs that are MOST SIMILAR in terms of their trait set. That is, all those category pairs that are mostly identical, with less than 5 traits don't match in between them. Is there a way to have Excel spit out a list of all the mostly-identical pairs?

I thought of the following steps we could do through if we were to do this manually, but with a large set of categories (67, so 67*67 is a REALLY big number) the number quickly becomes unmanageable.

1. List out every pair of categories in adjacent columns.
2. Use conditional formatting to shade unique values (traits) amongst each pair.
3. Use Excel's Count feature to identify the number of shaded values in each pair.
4. Identify and plot all the counts, filter by those with a count less than or equal to 5.
5. Bonus points if we can list out every discrepant trait for each pair.

I've pasted an extract of a sample category list so you can get a better picture. There are 4 categories here, so we would we taking Category 1 (ASC OR TECH_NA) and comparing it against Category 2 (IP VIEW ONLY WITH TREATMENT TEAM) and then Category 3 (IP CPOE VIEW ONLY) and so on.

[TABLE="width: 708"]
<tbody>[TR]
[TD]Category Name[/TD]
[TD]ASC OR TECH_NA[/TD]
[TD]IP VIEW ONLY WITH TREATMENT TEAM[/TD]
[TD]IP CPOE VIEW ONLY[/TD]
[TD]IP VIEW ONLY[/TD]
[/TR]
[TR]
[TD]Traits[/TD]
[TD]Build Custom Documents[/TD]
[TD]Build Custom Documents[/TD]
[TD]Build Custom Documents[/TD]
[TD]Build Custom Documents[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Patient Summary[/TD]
[TD]Chart Review[/TD]
[TD]Care Plan[/TD]
[TD]Charge Capture - Enter/Edit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Documentation Flowsheet[/TD]
[TD]Chart Search[/TD]
[TD]Chart Review[/TD]
[TD]Charge Capture - File[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Order Review[/TD]
[TD]Create Patient Staff Relationship[/TD]
[TD]Chart Search[/TD]
[TD]Chart Review[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]View Only Demographics[/TD]
[TD]Patient Lists[/TD]
[TD]Education Record[/TD]
[TD]Chart Search[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Order Entry[/TD]
[TD]Patient Summary[/TD]
[TD]Patient Lists[/TD]
[TD]Patient Lists[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Event Log[/TD]
[TD]Results Review[/TD]
[TD]Patient Summary[/TD]
[TD]Patient Summary[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Edit Results[/TD]
[TD]View Only Demographics[/TD]
[TD]Results Review[/TD]
[TD]Results Review[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]View Only Demographics[/TD]
[TD]View Only Demographics[/TD]
[/TR]
</tbody>[/TABLE]