Cross-referencing a large set of categories pair by pair

crackinthekraken

New Member
Joined
Mar 12, 2019
Messages
1
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 NameASC OR TECH_NAIP VIEW ONLY WITH TREATMENT TEAMIP CPOE VIEW ONLYIP VIEW ONLY
TraitsBuild Custom DocumentsBuild Custom DocumentsBuild Custom DocumentsBuild Custom Documents
Patient SummaryChart ReviewCare PlanCharge Capture - Enter/Edit
Documentation FlowsheetChart SearchChart ReviewCharge Capture - File
Order ReviewCreate Patient Staff RelationshipChart SearchChart Review
View Only DemographicsPatient ListsEducation RecordChart Search
Order EntryPatient SummaryPatient ListsPatient Lists
Event LogResults ReviewPatient SummaryPatient Summary
Edit ResultsView Only DemographicsResults ReviewResults Review
View Only DemographicsView Only Demographics

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Threads
1,109,444
Messages
5,528,797
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top