Avatar of Matt Pinkston
Matt Pinkston
 asked on

difference between two xls sheets

I have an XLS workbook with two sheets High5 and Defense but sheets have one column called opportunity id.  what I would like to do is create another sheet called difference which would show only differences:

Opportunity ID            Status
12345                            In High5 not in Defense
67899                            In Defense not in High5
Microsoft Excel

Avatar of undefined
Last Comment
Matt Pinkston

8/22/2022 - Mon

You can use VBA to make this easier, but would need an example workbook to give use the idea of the structure.

Or you can follow this:
    1) Copy the ID from High5 into the new sheet
    2) Copy the ID from Defense into new sheet
    3) Use Data > Remove Duplicates on the ID in the new sheet.
    4) Use COUNTIF on the new ID column back to High5... anying with zero is not in High5
    5) Use COUNTIF on the new ID column back to Defense... anying with zero is not in Defense
    6) delete those lines which were not Zero.
Alexandru Ungureanu

You could use VLOOKUP function in first sheet to get values from the second one (based on Opportunity ID), then filter data based on #N/A.
Same operation you'll perform on the second sheet, VLOOKUP and  filter. Copy results from filtered data and you are ready.

Regards, Alex.
Matt Pinkston

do you have a sample of this vlookup?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Alexandru Ungureanu

I've attached a sample.
It contains VLOOKUP and filter applied.
Hope you can simulate on your data.

BR, Alex.
Matt Pinkston