I need to reconcile an inventory! Help Feel like Dummy!

[raharms]

Hi everyone! My name is Robert and I am in a jam! Please help!
Ok, Ill try to make it simple...I have two sheets, One is a "physical count of items", the other is a "system on-hand" I want to merge them together to produce a "reconciliation" sheet.

Example:

Physical count sheet looks like this;

Col A Col B
Item Count

111111 1
222222 1
333333 3
444444 2
999999 1

On-hand Sheet looks like this

Col A Col B Col C
Item On-hand Avg Cost
111111 1 $1.00
222222 1 $1.00
333333 2 $1.00
444444 1 $1.00
555555 1 $1.00

As you can see, there is a need to reconcile, I would like to merge the two to produce something like this...

*NEW* Reconcile sheet

Col A Col B Col C Col D
Item On-hand Avg Cost Phys Count
111111 1 $1.00 1
222222 1 $1.00 1
333333 2 $1.00 3
444444 1 $1.00 2
555555 1 $1.00 0
999999 0 $0.00 1

*Note - item "555555" was not physically counted but is present in "on-hand" sheet & item "999999" was physically counted but did not match up with "on-hand" sheet; hence "zero" average cost.


If I had money I would pay you! But i don't

Thank you so much for your help!

 

[diddi]

HI and welcome. this is relative easy so you will soon be up and running. do you want the summary on a new sheet?

 

[raharms]

thanks for the quick response, Yes, I would like the results in a new sheet... there is about 5000 items and its making me crazy!

 

[Leith Ross]

Hello raharms,

I have posted an example workbook for you in the Excel Forum.

http://www.excelforum.com/excel-programming/757308-merging-a-physical-count-to-a-system-on-hand.html

Sincerely,
Leith Ross