Inventory Spreadsheet needs recognize certain values and adjust accordingly

[DKS]

I've searched already and couldn't quite find a solution. Any insight is greatly appreciated. I will be conducting an inventory of serialized product, quantities in the 1000's. I can run a report that lists everything we "should" have in our inventory. I want to copy the serial number data from that report and paste it into the new Inventory worksheet that I'll be using (Col B). From there, I will scan (usb Symbol scanner) all serial numbers that are physically on hand (Col A). I would like the spreadsheet to then auto delete whatever serial numbers are found in both columns, thus showing me any discrepancies between the report and physical inventory. Initially, I planned to utilize cell A2 as the "scanning cell" and build formulas around that, but it appears too difficult for me to do that.

I hope I was clear and concise enough. Thanks again.

[DKS]

Can anyone offer any thoughts? Do I need to give more details?

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[watersev]

hi, please post sample workbook with original data and result you need

[DKS]

Thanks. Give me a minute to post it.

Sample1.xls

It's my first time posting a sample file. Let me know if it didn't work.

The tentative plan is to scan the inventory items into the sheet first and then post the report data in last. I'm open to suggestions.

[watersev]

see attachment, run macro "test"

[DKS]

Sorry for taking so long to respond, but I was away from the computer for the remainder of the weekend. The first "test" was done with limited data and was succesful. I will test again this week with more data and hopefully close out the thread. Regardless, thank you for getting me this far.

[DKS]

Here's another concern I'm hoping can be fixed. Some of the data will contain entries that contain a hyphen but when scanning those same entries the hyphen will not be present. Is there a way to ignore the hyphens, so they are not counted as errors?

[watersev]

can you provide example and result that should be obtained?

[DKS]

Using the sample spreadsheet, the data in column A will not have a hyphen because the barcode doesn't contain it, so when it's scanned into the spreadsheet it won't be there. However column B is pasted data where the hyphen has been manually entered. When the macro is ran it calls out each number as an error. Not all entries will have hyphens in column B, only some. If you need further detail please let me know.

[watersev]

do you mean completely ignore all hyphens in column B?

[DKS]

Not unless it would ignore all hyphens in both columns. Some of the barcodes have the hyphens and some don't, so in some cases the hyphens wouldn't cause any issues because they would be in both columns. If the spreadsheet ignored all hyphens, i think that would work.

[DKS]

Once that data is in columns A & B, I could just highlight those columns, Edit/Replace all - with nothing and then run the macro, right? In your opinion that would work as well, right?

[watersev]

see attachment, I hope it helps. I've added deletion on hyphens for column B data

[DKS]

Can you go back to the original code you sent, and increase the range. I worked something else out for the hyphen issue. I just ran another live test and Column B was 3 entries shorter than Column A. When I ran "test" macro 3 three remaining entries on Column A did not appear in Column D. How can that be resolved?

My 2nd issue with the live test is there seems to be "identations" of some sort programmed into some of the barcodes. When these are scanned into Column A, and the same text sequence is pasted into Column B, they are both being called into Cloumns D & E as errors. Is there a way to eliminate that even if it's outside of writing code?

It's almost what I need it to be. Thanks so much for your help so far.

[DKS]

Ok. I was able to solve the second issue using the TRIM function, so I just need help with the range issue addressed last post.

[watersev]

see attachment

[DKS]

What I have works great the first time. Once content is cleared and the audit is attempted again I get a couple different issues and it gets worse the more times I clear content and try again. Do you know why this would occur?

[DKS]

I was going to post an updated version but it's too large. Any thoughts on how to solve would be great.

[DKS]

Can anyone explain why there would be no errors the first time but more and more errors each time it's used?