I have two separate lists of skills (from a skills taxonomy), i want to show which…
- User
I have two separate lists of skills (from a skills taxonomy), i want to show which skills appear in both lists and ones that appear in one but not the other - please could you think about how i can do this on excel?
Assistant
Here are several effective ways to compare your two skills lists in Excel:
Method 1: Using Formulas with Helper Columns
Setup: Put List A in column A, List B in column B, starting from row 2 (with headers in row 1).
Create these helper columns:
Column C - "In Both Lists":
=IF(AND(COUNTIF(A:A,A2)>0, COUNTIF(B:B,A2)>0), A2, "")Column D - "Only in List A":
=IF(AND(COUNTIF(A:A,A2)>0, COUNTIF(B:B,A2)=0), A2, "")Column E - "Only in List B":
=IF(AND(COUNTIF(B:B,B2)>0, COUNTIF(A:A,B2)=0), B2, "")Method 2: Conditional Formatting (Visual Approach)
- Select your first list
- Home tab → Conditional Formatting → New Rule
- Choose "Use a formula to determine which cells to format"
- Enter:
=COUNTIF($B:$B,$A2)>0 - Set formatting (e.g., green fill for matches)
- Repeat for the second list with formula:
=COUNTIF($A:$A,$B2)>0
Method 3: All-in-One Comparison
Create a master list in column F with all unique skills from both lists, then use:
Column G - "Source":
=IF(AND(COUNTIF(A:A,F2)>0,COUNTIF(B:B,F2)>0),"Both",IF(COUNTIF(A:A,F2)>0,"List A Only","List B Only"))Which method appeals to you most, or would you like me to walk through any of these in more detail?
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.