CODEWITHSUNDEEP
excel
Xaisoft
Xaisoft

Reputation: 46591

Find the differences between 2 Excel worksheets?

I have two excel files with the same structure: they both have 1 column with data. One has 800 records and the other has 805 records, but I am not sure which of the 5 in the 805 set are not in the 800 set. Can I find this out using Excel?

Upvotes: 62

Views: 398394

Answers (21)

David Leal
David Leal

Reputation: 6749

It is not clear from your question if you want to identify values not present in larger set or to check in the larger set if the value is present in the shorter one. Here a solution for both cases:

Values in Subset not in Set

=FILTER(B2:B11,ISNUMBER(MATCH(B2:B11,A2:A6,0)))

Check if value in Set is not in Subset

=IF(ISNUMBER(MATCH(B2:B11,A2:A6,0)), TRUE, FALSE)

sample excel file

Upvotes: 0

Aspak Rogatiya
Aspak Rogatiya

Reputation: 169

Tried to find a tool that will help to extract only the different sheets with the cell difference highlighted. Could not find any, so ended up writing one for myself. I hope this helps someone who is looking for similar solution. It takes care of left/right unique sheets, identical/different size sheets.

import pandas as pd
import xlsxwriter
import numpy as np
from openpyxl import load_workbook

# Get a complete list of sheets from both WorkBook
BOOK1 = "Book_1.xlsx"
BOOK2 = "Book_2.xlsx"

xlBook1 = pd.ExcelFile(BOOK1)
sheetsBook1 = xlBook1.sheet_names
xlBook2 = pd.ExcelFile(BOOK2)
sheetsBook2 = xlBook2.sheet_names

sheets = list(set(sheetsBook1 + sheetsBook2))

with pd.ExcelWriter('Difference.xlsx', engine='xlsxwriter', mode='w') as writer:
    for sheet in sheets:
        print (sheet)

        book1 = None
        book2 = None
        book1Exists = True
        book2Exists = True

        try:
            book1 = pd.read_excel(BOOK1,sheet_name=sheet,header=None,index_col=False).fillna(' ')
        except ValueError as ve:
            book1Exists = False
        try:
            book2 = pd.read_excel(BOOK2,sheet_name=sheet,header=None,index_col=False).fillna(' ')
        except ValueError as ve:
            book2Exists = False

        # Case 1: Both sheet exist and they are identical size
        if ( (( (book1Exists == True) and (book2Exists == True) )) and 
            ( (len(book1) == len(book2)) and (len(book1.columns) == len(book2.columns)) )):


                comparevalues = book1.values == book2.values
                if False in comparevalues:
                    rows,cols = np.where(comparevalues==False)

                    for item in zip(rows,cols):
                        book1.iloc[item[0],item[1]] = ' {} --> {} '.format(book1.iloc[item[0], item[1]], book2.iloc[item[0],item[1]])

                    book1.to_excel(writer,sheet_name=sheet,index=False,header=False)
                    
                    # Get the xlsxwriter workbook and worksheet objects.
                    workbook  = writer.book
                    worksheet = writer.sheets[sheet]
                    
                    # Add a format. Light red fill with dark red text.
                    format1 = workbook.add_format({'bg_color': '#FFC7CE',
                                                   'font_color': '#9C0006'})

                    # Apply a conditional format to the cell range.
                    worksheet.conditional_format('A1:AZ100',{'type':     'text',
                                                  'criteria': 'containing',
                                                  'value':    '-->',
                                                  'format':   format1}) 

        # Case 2: Left unique case        
        elif (book1Exists == False):
            book2.to_excel(writer,sheet_name=sheet+" B2U",index=False,header=False)
        
        # Case 3: Right unique case        
        elif (book2Exists == False):
            book1.to_excel(writer,sheet_name=sheet+" B1U",index=False,header=False)
        
        # Case 4: Both exist but different size
        elif (( (book1Exists == True) and (book2Exists == True) ) and 
            ( (len(book1) != len(book2)) or (len(book1.columns) != len(book2.columns)) )):
            if (book1.size > book2.size):
                book1.to_excel(writer,sheet_name=sheet+" SD",index=False,header=False)
            elif (book2.size > book1.size):
                book2.to_excel(writer,sheet_name=sheet+" SD",index=False,header=False)

Upvotes: 0

Doug Harris
Doug Harris

Reputation: 3369

I found this command line utility that doesn't show the GUI output but gave me what I needed: https://github.com/na-ka-na/ExcelCompare

Sample output (taken from the project's readme file):

> excel_cmp xxx.xlsx yyy.xlsx
DIFF  Cell at     Sheet1!A1 => 'a' v/s 'aa'
EXTRA Cell in WB1 Sheet1!B1 => 'cc'
DIFF  Cell at     Sheet1!D4 => '4.0' v/s '14.0'
EXTRA Cell in WB2 Sheet1!J10 => 'j'
EXTRA Cell in WB1 Sheet1!K11 => 'k'
EXTRA Cell in WB1 Sheet2!A1 => 'abc'
EXTRA Cell in WB2 Sheet3!A1 => 'haha'
----------------- DIFF -------------------
Sheets: [Sheet1]
Rows: [1, 4]
Cols: [A, D]
----------------- EXTRA WB1 -------------------
Sheets: [Sheet1, Sheet2]
Rows: [1, 11]
Cols: [B, K, A]
----------------- EXTRA WB2 -------------------
Sheets: [Sheet1, Sheet3]
Rows: [10, 1]
Cols: [J, A]
-----------------------------------------
Excel files xxx.xlsx and yyy.xlsx differ

Upvotes: 0

java-addict301
java-addict301

Reputation: 4108

The Notepad++ compare plugin works perfectly for this. Just save your sheets as .csv files and compare them in Notepad++. Notepad++ gives you a nice visual diff.

Upvotes: 3

pnuts
pnuts

Reputation: 59440

With just one column of data in each to compare a PivotTable may provide much more information. In the image below ColumnA is in Sheet1 (with a copy in Sheet2 for the sake of the image) and ColumnC in Sheet2. In each sheet a source flag has been added (Columns B and D in the image). The PT has been created with multiple consolidation ranges (Sheet1!$A$1:$B$15 and Sheet2!$C$1:$D$10):

SO1500153 exaple

The left hand numeric column shows what is present in Sheet1 (including q twice) and the right what in Sheet2 (again with duplicates – of c and d). d-l are in Sheet1 but not Sheet2 and w and z are in Sheet2 (excluding those there just for the image) but not Sheet1. Add display Show grand totals for columns and control totals would appear.

Upvotes: 0

Mchoeti
Mchoeti

Reputation: 536

SO in fact that you are using excel means that you can use the SpreadSheet Compare from Microsoft. It is available from Office 2013. Yes i know this question is older then 6 years. But who knows maybe someone need this information today.

Upvotes: 3

Jacob Kalakal Joseph
Jacob Kalakal Joseph

Reputation: 91

If you have Microsoft Office Professional Plus 2013, you can use Microsoft Spreadsheet Compare to run a report on the differences between two workbooks.

Launch Spreadsheet Compare:

In Windows 7: On the Windows Start menu, under Office 2013 Tools, click Spreadsheet Compare.

In Windows 8: On the Start screen, click Spreadsheet Compare. If you do not see a Spreadsheet Compare tile, begin typing the words Spreadsheet Compare, and then select its tile.

Compare two Excel workbooks:

  1. Click Home > Compare Files.
  2. a. Click the blue folder icon next to the Compare box to browse to the location of the earlier version of your workbook. (In addition to files saved on your computer or on a network, you can enter a web address to a site where your workbooks are saved.)
  3. b. Click the green folder icon next to the To box to browse to the location of the workbook that you want to compare to the earlier version, and then click OK. (TIP You can compare two files with the same name if they're saved in different folders.)
  4. In the left pane, choose the options you want to see in the results of the workbook comparison by checking or unchecking the options, such as Formulas, Macros, or Cell Format. Or, just Select All.

Reference:

https://support.office.com/en-us/article/Basic-tasks-in-Spreadsheet-Compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8

Upvotes: 5

krubo
krubo

Reputation: 6396

Easy way: Use a 3rd sheet to check.

Say you want to find differences between Sheet 1 and Sheet 2.

  1. Go to Sheet 3, cell A1, enter =IF(Sheet2!A1<>Sheet1!A1,"difference","").
  2. Then select all cells of sheet 3, fill down, fill right.
  3. The cells that are different between Sheet 1 and Sheet 2 will now say "difference" in Sheet 3.

You could adjust the formula to show the actual values that were different.

Upvotes: 17

Shashank Singla
Shashank Singla

Reputation: 1837

you should try this free online tool - www.cloudyexcel.com/compare-excel/

works good for most of the time, sometimes the results are a little off.

plus it also gives a good visual output

enter image description here

You can also download the results in excel format. (you need to signup for that)

Upvotes: 13

Marcus
Marcus

Reputation: 12586

Excel has this built in if you have an excel version with the Inquire add-in.

This link from office webpage describes the process of enabling the add-in, if it isn't activated, and how to compare two compare two workbooks - among other things.

The comparison shows both structural differances as well as editorial and a lot of other changes if http://office.microsoft.com/en-us/excel-help/what-you-can-do-with-spreadsheet-inquire-HA102835926.aspx

Upvotes: 13

Abtin Forouzandeh
Abtin Forouzandeh

Reputation: 5855

Use the vlookup function.

Put both sets of data in the same excel file, on different sheets. Then, in the column next to the 805 row set (which I assume is on sheet2), enter

=if(isna(vlookup(A1, Sheet1!$A$1:$A$800, 1, false)), 0, 1)

The column will contain 0 for values that are not found in the other sheet, and 1 for values that are. You can sort the sheet to find all the missing values.

Upvotes: 3

user2601995
user2601995

Reputation: 6803

ExcelDiff exports a HTML report in a Divided (Side-by-side) or Merged (Overlay) view highlighting the differences as well as the row and column.

Upvotes: 2

Sun
Sun

Reputation: 2715

I used Excel Compare. It is payware, but they do have a 15 day trial. It will report amended rows, added rows, and deleted rows. It will match based on the worksheet name (as an option):

http://www.formulasoft.com/excel-compare.html

Upvotes: 1

hawbsl
hawbsl

Reputation: 16003

COUNTIF works well for quick difference-checking. And it's easier to remember and simpler to work with than VLOOKUP.

=COUNTIF([Book1]Sheet1!$A:$A, A1)

will give you a column showing 1 if there's match and zero if there's no match (with the bonus of showing >1 for duplicates within the list itself).

Upvotes: 8

arober11
arober11

Reputation: 2019

LibreOffice provides a Workbook Compare feature: Edit -> Compare Document

Upvotes: 19

xman_lives
xman_lives

Reputation: 39

I think your best option is a freeware app called Compare IT! .... absolutely brilliant utility and dead easy to use. http://www.grigsoft.com/wincmp3.htm

Upvotes: 3

KBBWrite
KBBWrite

Reputation: 4409

May be this replay is too late. But hope will help some one looking for a solution

What i did was, I saved both excel file as CSV file and did compare with Windiff.

Upvotes: 2

NickSentowski
NickSentowski

Reputation: 818

vlookup is your friend!

Position your column, one value per row, in column A of each spreadsheet. in column B of the larger sheet, type

=VLOOKUP(A1,'[Book2.xlsb]SheetName'!$A:$A,1,FALSE)

Then copy the formula down as far as your column of data runs.

Where the result of the formula is FALSE, that data is not in the other worksheet.

Upvotes: 39

Sauron
Sauron

Reputation:

excel overlay will put both spreadsheets on top of each other (overlay them) and highlight the differences.

http://download.cnet.com/Excel-Overlay/3000-2077_4-10963782.html?tag=mncol

Upvotes: -1

eric.a.booth
eric.a.booth

Reputation: 651

Use conditional formatting to highlight the differences in excel.

Here's an example.

Upvotes: 0

Juliet
Juliet

Reputation: 81516

It might seem like a hack, but I personally prefer copying the cells as text (or exporting as a CSV) into Winmerge or any other diff tool. Assuming the two sheets contain mostly identical data, Winmerge will show the differences immediately.

Upvotes: 21

Related Questions