Perform an analysis of inventory variance against a Pre & Post count list.

[8lighting]

Hello,

This is my first post to the forum and I'm in the early days of unlocking the power of excel

I would like assistance with my efforts to perform a variance analysis of an inventory cycle count. The info I am after is the difference in the pre-count qty and the post-count qty for a given PN. This info is contained on two separate lists. I have tried the following VLOOKUP,
=VLOOKUP(A6,'Cycle Count List POST'!$A$7:$B$10,2)
which works until the point that the list differs between the Pre-count and Post-count part numbers.

PART	PRE COUNT QTY	POSTCOUNT QTY	DIFF	%
10094	420	419
10095	153	177
10111-01	104	#N/A
10197	3848	3375
10199	3915	2636
10204	1214	2636

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

The Post Count part list is contained on a different tab and contains approximately 100 less entries as part of the cycle count some lines have been deleted or consolidated.

My VLOOKUP throws an error on PN 10111-01 because the Post-count List does not contain the same PN in that position. Could this be controlled with IFERROR?

The other condition that gives me trouble is:
On the Pre-Count list there are several identical PN's with different quantities.

11133-K-HI-500	75
11133-K-HI-500	87
11133-K-HI-500	123

<colgroup><col><col></colgroup><tbody>
</tbody>

And on the post count list there are several identical PN's with different quantities.

11133-K-HI-500	8
11133-K-HI-500	8
11133-K-HI-500	75
11133-K-HI-500	87

<colgroup><col><col></colgroup><tbody>
</tbody>

The difference distinction between the PN's was Lot#, though that info is not contained within the lists and is not really need for this evaluation. It would be acceptable for all the identical PN's (11133-K-HI-500) to be summed into one quantity for both Pre & Post count values.

Thank you in advance for your assistance.

 

[AngelEG]

8lighting,

For you problem you can solve it by changing your strategy from a vlookup to a sumif.

Here's a possible solution that addresses both the vlookup and multiple entry requirements:

Formula on C5 sums range G5:G12 only if row range F5:F12 matches A5, so the formula is =SUMIF($F$5:$F$12,A5,$G$5:$G$12)

This will bring the data from your other sheet regardless of position or number of entries.

Hope it helps, if you have additional questions let me know.

Angel

Sheet1

	A	B	C	D	E	F	G
4	PART	PRE COUNT QTY	POSTCOUNT QTY	DIFF		PART	POSTCOUNT QTY
5	10094	420	415	98.8%		10094	415
6	10095	153	144	94.1%		10095	144
7	10111-01	104	99	95.2%		10111-01	90
8	10197	3848	3777	98.2%		10111-01	9
9	10199	3915	2788	71.2%		10197	3777
10	10204	1214	2200	181.2%		10199	2788
11						10204	600
12						10204	1600

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C5 =SUMIF($F$5:$F$12,A5,$G$5:$G$12) D5 =C5/B5 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4