Link to home
Start Free TrialLog in
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
Avatar of Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Alexandru Ungureanu
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.
Avatar of Matt Pinkston


do you have a sample of this vlookup?
Avatar of NBVC
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've attached a sample.
It contains VLOOKUP and filter applied.
Hope you can simulate on your data.

BR, Alex.