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.
[TABLE="width: 433"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]PART[/TD]
[TD="align: center"]PRE COUNT QTY[/TD]
[TD="align: center"]POSTCOUNT QTY[/TD]
[TD]DIFF[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]10094[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]419[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10095[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]177[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10111-01[/TD]
[TD="align: right"]104[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10197[/TD]
[TD="align: right"]3848[/TD]
[TD="align: right"]3375[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10199[/TD]
[TD="align: right"]3915[/TD]
[TD="align: right"]2636[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10204[/TD]
[TD="align: right"]1214[/TD]
[TD="align: right"]2636[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 187"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]11133-K-HI-500[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]11133-K-HI-500[/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD]11133-K-HI-500[/TD]
[TD="align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 187"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]11133-K-HI-500[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]11133-K-HI-500[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]11133-K-HI-500[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]11133-K-HI-500[/TD]
[TD="align: right"]87[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #cacaca"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD]PART[/TD]
[TD="align: center"]PRE COUNT QTY[/TD]
[TD="align: center"]POSTCOUNT QTY[/TD]
[TD]DIFF[/TD]
[TD][/TD]
[TD]PART[/TD]
[TD="align: center"]POSTCOUNT QTY[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]10094[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]415[/TD]
[TD="align: right"]98.8%[/TD]
[TD][/TD]
[TD="align: right"]10094[/TD]
[TD="align: right"]415[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]10095[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]94.1%[/TD]
[TD][/TD]
[TD="align: right"]10095[/TD]
[TD="align: right"]144[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD]10111-01[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]95.2%[/TD]
[TD][/TD]
[TD]10111-01[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]10197[/TD]
[TD="align: right"]3848[/TD]
[TD="align: right"]3777[/TD]
[TD="align: right"]98.2%[/TD]
[TD][/TD]
[TD]10111-01[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]10199[/TD]
[TD="align: right"]3915[/TD]
[TD="align: right"]2788[/TD]
[TD="align: right"]71.2%[/TD]
[TD][/TD]
[TD="align: right"]10197[/TD]
[TD="align: right"]3777[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]10204[/TD]
[TD="align: right"]1214[/TD]
[TD="align: right"]2200[/TD]
[TD="align: right"]181.2%[/TD]
[TD][/TD]
[TD="align: right"]10199[/TD]
[TD="align: right"]2788[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10204[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10204[/TD]
[TD="align: right"]1600[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet Formulas
[TABLE="class: grid, width: 500"] <tbody>[TR="bgcolor: #cacaca"] [TD]Cell	Formula
C5	=SUMIF($F$5:$F$12,A5,$G$5:$G$12)
D5	=C5/B5

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]


Excel tables to the web >> Excel Jeanie HTML 4