Skip to content
TheChatArchiveThe Chat Archive
DeepSeekdeepseek-r1-0528· DeepSeekHugging Face LMArena Human Preference 140K

I have 2 excel sheets in same file. how do i find those emails that appear in sheet 1,…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with DeepSeekdeepseek-v3-0324’s answer.
  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?

  2. DeepSeek Assistant

    To find emails in Sheet1 that do not appear in Sheet2 in the same Excel file, follow these steps:


    Method 1: Using the COUNTIF Formula (Simple & Recommended)

    1. Add a Helper Column in Sheet1:
      In Sheet1, insert a new column next to your emails (e.g., if emails are in Column A, use Column B).

    2. Enter the Formula:
      In cell B2 of Sheet1 (assuming A2 is the first email), enter:

      =IF(COUNTIF(Sheet2!A:A, A2)=0, "Not in Sheet2", "")
      
      • Replace Sheet2!A:A if emails in Sheet2 are in a different column (e.g., Sheet2!C:C).
    3. Drag the Formula Down:
      Double-click the fill handle (bottom-right corner of cell B2) to apply the formula to all rows.

    4. Identify Results:
      Emails marked with "Not in Sheet2" in Column B are missing from Sheet2.


    Method 2: Using VLOOKUP

    1. Add a Helper Column in Sheet1:
      In Sheet1, use Column B.

    2. Enter the Formula:
      In cell B2 of Sheet1:

      =IF(ISNA(VLOOKUP(A2, Sheet2!A:A, 1, FALSE)), "Not in Sheet2", "")
      
    3. Drag the Formula Down:
      Apply to all rows. Emails with "Not in Sheet2" are missing in Sheet2.


    Method 3: Conditional Formatting (Visual Highlight)

    1. Select Email Cells in Sheet1:
      Highlight the email range in Sheet1 (e.g., A2:A100).

    2. Go to Home > Conditional Formatting > New Rule:
      Select "Use a formula to determine which cells to format".

    3. Enter Formula:
      Use:

      =COUNTIF(Sheet2!A:A, A2)=0
      
    4. Set Format:
      Choose a highlight color (e.g., red fill).
      Click OK. Emails not in Sheet2 will be highlighted.


    Method 4: Advanced Filter (Extract to New Location)

    1. Go to Data > Advanced (under "Sort & Filter").
    2. Set:
      • List Range: Sheet1!A:A (emails in Sheet1).
      • Criteria Range: Leave blank.
      • Check "Unique records only".
      • Select "Copy to another location".
      • Copy to: E.g., Sheet1!C1.
    3. Use Formula for Comparison:
      In D2 (next to the extracted list), enter:
      =IF(COUNTIF(Sheet2!A:A, C2)=0, "Not in Sheet2", "")
      
      Drag down to identify missing emails.

    Summary

    • Quick Check: Use COUNTIF in a helper column (Method 1).
    • Visual Aid: Apply Conditional Formatting (Method 3).
    • Extract Data: Use Advanced Filter (Method 4) for a separate list.

    Adjust column references (A:A, Sheet2) based on your data structure.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.