Creating a Variance report in Excel ??

[PBB13]

Hi All--

Is it possible to use Excel to make a variance report ? I have two sheets- one showing the starting qty's and the other showing the qty's that are really there-
Each has two column's--part # and qty. If I assume that both have the same part #'s I can make one-ugly as it is and slow to create- by pasting one on the other-sort it and use the subtotal function--Then use a formula for the math and just copy it down-The problem is when I either have #'s that are on the first and not the other or the other way around it does not work right--

Thank you for the help----Paul

 

[JVFriederick]

One way or the other you really need both quantity values on the same sheet, AND you need to know when you are missing either the begin qty's or current qty's so you can decide what to do with those situations.


One method to get the data together:

Say Sheet1 has Part#'s, BeginQty in column A,B
Sheet2 has Part#'s, CurrentQty in column A,B
Copy Sheet2-A to Sheet1-A under existing data, copy Sheet2-B to Sheet1-C

Sort columns A,B,C by column A
In column D write a formula (in row 9 in this example) to place the values on the same row if the part #'s match. The formula below "brings up" the current quantity if a part# match in found

=IF(A10=A9,C10)

Copy/PasteValues to get rid of the formulas and then sort again by column D (this will group all matches together.

Sorry to be brief, and hope this helps!