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.

Category Name	ASC OR TECH_NA	IP VIEW ONLY WITH TREATMENT TEAM	IP CPOE VIEW ONLY	IP VIEW ONLY
Traits	Build Custom Documents	Build Custom Documents	Build Custom Documents	Build Custom Documents
	Patient Summary	Chart Review	Care Plan	Charge Capture - Enter/Edit
	Documentation Flowsheet	Chart Search	Chart Review	Charge Capture - File
	Order Review	Create Patient Staff Relationship	Chart Search	Chart Review
	View Only Demographics	Patient Lists	Education Record	Chart Search
	Order Entry	Patient Summary	Patient Lists	Patient Lists
	Event Log	Results Review	Patient Summary	Patient Summary
	Edit Results	View Only Demographics	Results Review	Results Review
			View Only Demographics	View Only Demographics

<tbody>
</tbody>