Excel compare data from two sheets

Question

I have two pricing sheets I need to compare. There are 4 columns. Part number, description, percent discount and price.

I need to compare the old sheet to the new to find any pricing and percent updates.

Since the list of part numbers can add ore remove from sheet to sheet it could not compare by row and would need to do something like this.

Look at first part number in sheet one. (old price list)

Locate same part number in sheet two. (new price list)

Compare percent and price and if different then highlight in sheet two.

Answer 1

Export both Excel Sheets to CSV Format
Compare the text differences using a file compare tool

My approach (which I often use) is to export both sheets to CSV Format and compare both text files. Therefore I have written these two files:

Xls2Csv.vbs

'Script for conversion of XLS-/XLSX-Files to CSV-Format
'Michael Hutter / April 2016

if WScript.Arguments.Count < 1 Then
    WScript.Echo "There has to be at least one parameter!" & vbCrLf & _
                 "Syntax:" & vbCrLf & _
                 "  Xls2Csv.vbs SourceFile.xls DestFile.xls\n" & vbCrLf & _
                 "  Xls2Csv.vbs SourceFile.xls (a file called SourceDate.xls.csv will be written)"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
if WScript.Arguments.Count = 1 then
  dest_file = src_file & ".csv"
else
  dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
end if

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

Xls2Csv.bat: (you might not need it) ;-)

start %cd%\Xls2Csv.vbs %1 %2 %3

If you have installed Git you can also use the following Batch file to either compare two xls files via the Excel-internal compare function or automated CSV Export (using the two files above).

DiffScript_xls.bat

@echo off
if X%1==X goto Syntax
goto SyntaxOk

:Syntax
echo This script either compares two xls-/xlsx-files with the Excel-internal compare function
echo or by exporting the sheet content to CSV format and comparing the text files.
echo Written by Michael Hutter in April 2016
echo.
echo Syntax:
echo %0 File1.xls File2.xls
pause
goto Ende

:SyntaxOk
set Datei=%0
set Rcksprung=Pos0
goto TeileDateipfadAuf
:Pos0
rem Pfad ermitteln, in dem die Batch-Datei liegt...
set WorkPfad=%LAUFWERK%%PFAD%
rem Leider liegt die Batch-Datei nicht am Urpsrungsort, da durch die Compilierung zur EXE während der Laufzeit ein Entpacken in einen anderen Ordner erfolgt :-(
set WorkPfad=C:\Tools\Batch\Diff-Scripts\

set base=%1
set mine=%2
echo Compared are the following files:
echo   Base-File=%base%
echo   Mine-File=%mine%
echo.
echo Do you want to compare by [E]xcel or by [C]sv-Format?
C:\Windows\System32\choice /C ec /T 2 /D c
if errorlevel 2 goto CsvVergleich
if errorlevel 1 goto ExcelVergleich

:ExcelVergleich
echo Comparing by Excel...
rem Datei1 vorbereiten
set Datei=%base%
set Rcksprung=PosExcel1
goto TeileDateipfadAuf
:PosExcel1
echo Bei PosExcel1
echo ###
echo LAUFWERK=%LAUFWERK%
echo PFAD=%PFAD%
echo DATEI=%DATEI%
echo ERWEITERUNG=%ERWEITERUNG%
echo ###
ren "%LAUFWERK%%PFAD%%DATEI%%ERWEITERUNG%" "%DATEI%(base)%ERWEITERUNG%"
wscript.exe "C:\Program Files\TortoiseGit\Diff-Scripts\diff-xls.js" "%LAUFWERK%%PFAD%%DATEI%(base)%ERWEITERUNG%" %mine% //E:javascript
goto Ende

:CsvVergleich
echo Comparing by CSV...

set Datei=%1
set Rcksprung=Pos1
goto TeileDateipfadAuf
:Pos1
echo Bei Pos1
echo ###
echo LAUFWERK=%LAUFWERK%
echo PFAD=%PFAD%
echo DATEI=%DATEI%
echo ERWEITERUNG=%ERWEITERUNG%
echo ###

set Datei1=%DATEI%%ERWEITERUNG%
if not exist %TEMP%\CSV1\NUL md %TEMP%\CSV1
if exist "%TEMP%\CSV1\%Datei1%.csv" del "%TEMP%\CSV1\%Datei1%.csv"
if exist "%TEMP%\CSV1\%Datei1%.csv" echo Can't delete File1: "%TEMP%\CSV1\%Datei1%.csv"
copy "%LAUFWERK%%PFAD%%DATEI%%ERWEITERUNG%" "%TEMP%\CSV1"
echo Converting File1 to CSV-Format: "%TEMP%\CSV1\%Datei1%"
%WorkPfad%Xls2Csv.vbs "%TEMP%\CSV1\%Datei1%"

rem Prepare File2
set Datei=%mine%
echo Datei=%Datei%
set Rcksprung=Pos2
goto TeileDateipfadAuf
:Pos2
echo Bei Pos2
echo ####
echo LAUFWERK=%LAUFWERK%
echo PFAD=%PFAD%
echo DATEI=%DATEI%
echo ERWEITERUNG=%ERWEITERUNG%
echo ####
set Datei2=%DATEI%%ERWEITERUNG%
if not exist %TEMP%\CSV2\NUL md %TEMP%\CSV2
if exist "%TEMP%\CSV2\%Datei1%.csv" del "%TEMP%\CSV2\%Datei1%.csv"
if exist "%TEMP%\CSV2\%Datei1%.csv" echo Can't delete file: "%TEMP%\CSV2\%Datei1%.csv"
copy %mine% "%TEMP%\CSV2"
echo Converting File2 to CSV-Format: "%TEMP%\CSV2\%Datei2%"
%WorkPfad%Xls2Csv.vbs "%TEMP%\CSV2\%Datei2%"

"C:\Program Files\TortoiseGit\bin\TortoiseGitMerge.exe" "%TEMP%\CSV1\%Datei1%.csv" "%TEMP%\CSV2\%Datei2%.csv"
goto Ende

:TeileDateipfadAuf
for /f "delims= tokens=1,2" %%i in ("%Datei%") do (
set "LAUFWERK=%%~di"
set "PFAD=%%~pi"
set "DATEI=%%~ni"
set "ERWEITERUNG=%%~xi"
)
goto %Rcksprung%

:Ende

Answer 2

I find myself doing something similar a lot. I found the best way is to create a third sheet which will only show the differences.

=IF(Sheet2!A1 =VLOOKUP(Sheet2!$A1,Sheet1!$A:A,COLUMN(Sheet1!A1),FALSE),"",Sheet2!A1 & " / " & VLOOKUP(Sheet2!$A1,Sheet1!$A:A,COLUMN(Sheet1!A1),FALSE))

If you put this in cell a1 in a new sheet (replacing sheet names as appropriate) then drag it across for all your columns, and then down for the rows it will show the values that differ and nothing else.

You can then apply a filter to make it more visible.

This assumes your part number column is column A, and will only appear once in each list.