deepseek-v3-0324· DeepSeek LMArena Human Preference 140K
I have 2 excel sheets in same file. how do i find those emails that appear in sheet 1,…
- User
I have 2 excel sheets in same file. how do i find those emails that appear in sheet 1, but don't appear in sheet 2?
- Assistant
To find emails that appear in Sheet1 but not in Sheet2 in the same Excel file, you can use one of the following methods:
Method 1: Using VLOOKUP (Easiest)
-
Assume:
- Sheet1 has emails in Column A (e.g.,
A2:A100). - Sheet2 has emails in Column A (e.g.,
A2:A100).
- Sheet1 has emails in Column A (e.g.,
-
In Sheet1, add a new column (e.g., Column B) and enter:
=IF(ISNA(VLOOKUP(A2, Sheet2!A:A, 1, FALSE)), "Not in Sheet2", "")- This checks if the email in
A2(Sheet1) exists inSheet2!A:A. - If not found, it returns "Not in Sheet2".
- This checks if the email in
-
Drag the formula down to apply it to all rows.
-
Filter Column B for "Not in Sheet2" to see the missing emails.
Method 2: Using Conditional Formatting (Visual)
- Select the email column in Sheet1 (e.g.,
A2:A100). - Go to Home → Conditional Formatting → New Rule.
- Choose "Use a formula to determine which cells to format".
- Enter:
=COUNTIF(Sheet2!A:A, A2)=0 - Set a format (e.g., red fill) and click OK.
- Emails in Sheet1 that don’t exist in Sheet2 will be highlighted.
Method 3: Using Power Query (Best for Large Data)
- Go to Data → Get Data → From Table/Range (for both sheets).
- In Power Query Editor:
- Select Sheet1 → Home → Merge Queries.
- Choose Left Anti Join (keeps only rows in Sheet1 that don’t match Sheet2).
- Select the email columns as key fields.
- Click OK → Close & Load to get the unmatched emails.
Method 4: Using FILTER (Excel 365/2021)
If you have Excel 365 or 2021, use:
=FILTER(Sheet1!A2:A100, ISNA(MATCH(Sheet1!A2:A100, Sheet2!A2:A100, 0)))This returns only emails in Sheet1 that don’t appear in Sheet2.
Result
- VLOOKUP → Simple but requires a helper column.
- Conditional Formatting → Quick visual check.
- Power Query → Best for automation & large datasets.
- FILTER → Cleanest (if available in your Excel version).
Let me know if you need help with any specific method!
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.