Create a search showing difference between Lot Number On Hand / Available and Location On Hand and Available

1. Create an item search to show Lot Number On Hand and Available:
 
a. Navigate to Reports>New Search
b. Select Item from the Search Types list
c. Under Results tab, select the following fields:
 
Internal ID
Name
Inventory Number fields : Number
Inventory Number fields : Location
Inventory Number fields : On Hand
Inventory Number fields : Available
 
d. Hit Preview and export to Microsoft Excel
 
2. Create an item search to show Location On Hand and Available:  
a. Navigate to Reports>New Search
b. Select Item from the Search Types list
c. Under Results tab, select the following fields:
 
Internal ID
Name
Location On Hand
Location Available
 
d. Hit Preview and export to Microsoft Excel
 
3. You can then merge the two spreadsheet into one excel workbook and you can use either a VLOOKUP function or SUMIF function to compare the differences.