Excel Function to Compare Pricing on Multiple Line Items

[arstark]

Hi Everyone!

I am not sure if there has been a question like the one I am about to ask. I have tried searching on Google for months now to see if there is an easier way to help me compare pricing on excel spreadsheets that are sometimes 10,000 line items long without doing it manually.

I have attached an example spreadsheet for any one to see what I am referring too. I am in the restaurant industry. I am in charge of making sure our vendors are charging us our agreed upon contract pricing for our food items. I run a quarterly report that is exported into Excel. I manually go through and pull out all the line items that are on "CONTRACT PRICING." I then move them to a different tab on my spreadsheet. Then I filter the spreadsheet either by product number or brand. Once I have it filtered, I manually type in a new column "contract price" column what the contract price is. I add another column that I enter a formula to calculate the difference. I then add another column that I enter a formula to calculate how much the vendor owes us for any overcharges based of the difference the charged us per case.

Does anyone know of how I can make these easier by inputting rules or something that will auto calculate this for me? If I am not making any sense, I completely understand, but hopefully by looking at my spreadsheet you will be able to see what I am trying to explain and what I am asking.

Any help, ideas or suggestions are greatly appreciated!

Sample Spreadsheet.xlsx

Thanks in advance!

[mikeTRON]

So the worksheet is exactly what you get as data, or it is what you have already manipulated?

[arstark]

I have manipulated it because there is so much other information on there that I don't need.

[mikeTRON]

So I guess, I would need to know what the starting point is and what the ending point looks like and any additional data you need to bring in and then I can help.