Creating a variance Report

PBB

New Member
Joined
May 10, 2003
Messages
12
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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")

paddy
 

Arnold23

New Member
Joined
Dec 28, 2014
Messages
1
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.

Thanks in advance.

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<form name="formCb097197"><input *******="window.clipboardData.setData("Text",document.formFb029964.sltNb561987.value);" type="button" value="Copy Formula" name="btCb936858"></form>

<tbody>
</tbody>
<select onchange="document.formFb029964.txbFb105945.value = document.formFb029964.sltNb561987.value" name="sltNb561987"><option value="=IF(COUNTIF($A$2:$A$6,D2),VLOOKUP(D2,$A$2:$B$6,2,0)-E2,"Not on List")" selected="">F2</option><option value="=IF(COUNTIF($A$2:$A$6,D3),VLOOKUP(D3,$A$2:$B$6,2,0)-E3,"Not on List")">F3</option><option value="=IF(COUNTIF($A$2:$A$6,D4),VLOOKUP(D4,$A$2:$B$6,2,0)-E4,"Not on List")">F4</option><option value="=IF(COUNTIF($A$2:$A$6,D5),VLOOKUP(D5,$A$2:$B$6,2,0)-E5,"Not on List")">F5</option><option value="=IF(COUNTIF($A$2:$A$6,D6),VLOOKUP(D6,$A$2:$B$6,2,0)-E6,"Not on List")">F6</option></select>=<input size="80" value="=IF(COUNTIF($A$2:$A$6,D2),VLOOKUP(D2,$A$2:$B$6,2,0)-E2,"Not on List")" name="txbFb105945">

<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")

paddy
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top