Stock inventory problem.

[SwiftandSure]

Hi there,

New member, first post here.

I'm a bit excel thick and require some assistance from the database ninjas among you. I’m implementing a new stock management system for all our hardware at work. I’ve audited everything we have and now I want to develop an excel workbook with the following sheets:

Incoming Stock Sheet: This is where I register the part numbers and serial numbers of the kit.
Outgoing Stock Sheet: In this sheet, I input the serial number using a barcode scanner, once that serial number is in the sheet, “vlookup” populates the rest of the row with the equipment details from the Incoming Stock sheet.
Live Stock Sheet: This is where I’m stuck, I basically want this to be an automatically generated worksheet that shows what’s on the Incoming Stock list, but removes anything that shows up in the Outgoing Stock list using the serial numbers as the common reference between them. Leaving me with a database of whatever’s left in Stock.

I've got as far as having "=Incoming Stock" in the Live Stock Sheet, which automatically generates the incoming stock, but I'm still none the wiser as how to remove the entries that match the Outgoing Stock worksheet.

Is this a simple fix with a few formulas? Or am I delving into macro territory here?

All replies and PMs welcome.

Many thanks in advance.

[cheeze83]

Hi

The below code assumes that the bar code is in column A, so if it's not - change the column that inserts from B to the column next to the bar code.

The idea of this is that in sheet 3, it will copy the data from sheets 1 and 2, then count the barcodes to find a duplicate - where it is found, it will remove both occurances, keeping just the one

Please Login or Register  to view this content.

[SwiftandSure]

Thanks Cheeze83,

I've attached a sample copy of the sheet I'm working from.

This is my first time using VBA, so I'm a bit overwhelmed. Can you please have a quick look over the workbook and your script to see if it'll do the job? I amended the script to what I thought was reflecting my workbook, but I think I did something wrong as my machine has all but crashed trying to run it.

[cheeze83]

Storeroom Stock Cheeze83.xlsmNo worries at all - I;ve added it in for you and made the changes so it should work, have attached

What you might need to do, if the developer tab isn't showing, is go to Excel Options and click 'Show Developer Tab'

Then click on the tab, click Macro, select inventory and click Run - and hopefully should work =)

[SwiftandSure]

That's done the trick!

Thanks so much! I really appreciate the help.

[cheeze83]

No worries at all - glad it worked =)

[SwiftandSure]

It looks like I have another problem.

I've built the sheet using Excel 2010, but it'll mainly be used on a machine in our storeroom that's running Excel 2003.

The problem is, when I go to run the macro on the machine with Excel 2003, it is outputting the contents of the Outgoing Stock at the bottom of the Live Stock Sheet and if I continue to run the macro, it duplicates those values again and again.

Do I need to save the sheet as a particular file type or is there a script conflict between Excel 2003 and 2010?

[cheeze83]

Unfortunately I'm not too sure, I've not worked in 2003 for quite a while - I'm going to try and get hold of a copy and run the macro, but it would be worth making sure you're saving the 2010 in compatability mode.

[SwiftandSure]

It's an odd one. When I run it on the 2003 machine it duplicates the outputs. But when I save that workbook with duplications and run it on the 2010 machine it corrects the mistakes and works fine. Very odd.

I tried it as a 2003-2007 workbook too, still no joy.

[cheeze83]

Hi - sorry been a while between posts. I haven't been able to get hold of Excel 2003 at the moment, however I'm waiting for my work computer to be fixed which has excel 2007 on it - think there is less comptability issues between 2003 and 2007 than 2003 and 2010, so as soon as thats up and running going to run the code again for you in 2007 and see what I can find. I just need work to pull their fingers out now.....it's been broken a week....