# Creating a variance Report

#### PBB

##### New Member
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

##### MrExcel MVP
HI - welcome to the board!

If I've understood you right, the following may help:
Book1
ABCDEF
1PartQty1PartQty2Variance
2a1a2
3b2b3
4c3c4
5d4e5
6e5f6
Sheet1

the formula is:

=IF(COUNTIF(\$A\$2:\$A\$6,D2),VLOOKUP(D2,\$A\$2:\$B\$6,2,0)-E2,"Not on List")

#### Arnold23

##### New Member
Hi mr Excel,

This is very useful. I would like to add a question on top of this. How can you auto highlight the part# or Qty*1 if they were not counted?
just to flag us that it was not counted and missed.

Happy Holidays.

Arnold

HI - welcome to the board!

If I've understood you right, the following may help:

******** ******************** ************************************************************************><center>
 Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000

<tbody>
</tbody>
 File Edit View Insert Options Tools Data Window Help About

<tbody>
</tbody>
 =

<tbody>
</tbody>
<center>A</center><center>B</center><center>C</center><center>D</center><center>E</center><center>F</center>
<center>1</center>PartQty*1*PartQty*2Variance
<center>2</center>a1*a2-1
<center>3</center>b2*b3-1
<center>4</center>c3*c4-1
<center>5</center>d4*e5
<center>6</center>e5*f6Not*on*List
 Sheet1 *

<tbody>
</tbody>

<tbody>
</tbody>

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

the formula is:

=IF(COUNTIF(\$A\$2:\$A\$6,D2),VLOOKUP(D2,\$A\$2:\$B\$6,2,0)-E2,"Not on List")

1,082,287
Messages
5,364,309
Members
400,787
Latest member
bs04c

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...