Creating a variance Report

[PBB]

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]

HI - welcome to the board!

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

Book1
	A	B	C	D	E	F
1	Part	Qty1		Part	Qty2	Variance
2	a	1		a	2	-1
3	b	2		b	3	-1
4	c	3		c	4	-1
5	d	4		e	5	0
6	e	5		f	6	NotonList
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]

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>	Part	Qty*1	*	Part	Qty*2	Variance
<center>2</center>	a	1	*	a	2	-1
<center>3</center>	b	2	*	b	3	-1
<center>4</center>	c	3	*	c	4	-1
<center>5</center>	d	4	*	e	5
<center>6</center>	e	5	*	f	6	Not*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