need excel wizards help

[adelburn]

I have been asked to do a manual stock count at work tomorrow. This will involve physically counting over 5000 parts

Being the lazy sod I am I was thinking how to speed things and as we actually have scanners at work and all the parts are barcoded I thought that I could produce 2 reports. 1 with what is in stock on the system and 1 what i have scanned and cross match them both.

Obviously this meens some sort of formula, and I presume that formula is a v-lookup, but I am not sure how/what to put in the formula. I have moderate experience with excel just need some kind soul to help ease my day tomorrow so I can watch por....erm.... show initiative at work

any help much appreciated

Oh ye each part has an individual License plate and report 1 would have part number in column A, License plate in B, location in C.

Report 2 would be License plate in A, location in B.

 

[xirokx]

vlookup would auto push figures into the relevent cell so for example if you had 2 part numbers and your manual scan said 10 items but the system said 20 vlookup would take the figure you specify either 10 or 20 and put it inside a "nominated" cell....

I am not sure vlookup would do the trick...

I would use a single worksheet, auto arrange them item ref's in your chosen order so you have your scan in column a, column b would be your manual count, column c would be system item no and column d would be system count....

Then make a column E name it total, and do a sum which would be "manual scan items - system count" and then stick a filter on column E and it will show you all the items which are lets say "greater than 0"

This will only work if your system has all the item ref numbers and if they match what you scan in....

Hope you get my jist but bascially thats an easy way of doing it, unless some excel buff has another idea??

 

[adelburn]

Cheers for that xirokx, the only problem, as you said, would be it would not show extra /missing parts.

I think I have sussed it though. I used the LOOKUP not VLOOKUP to search for a License plate from report 1 in column A of report 2 and return with the location it was scanned in and then in a seperate column do an IF formula to see if the location that is returned is the same as that on the system.

This is probably the long way round and there is no doubt a quicker way, but it works so I'm happy.

Cheers