Inventory management - Matching and sorting barcode scanned with existing spreadsheet info

Hi,

Welcome to the Forum.

Can you please post a sample template of your workbook using "Go Advanced" option. You may choose to delete the confidential data in the file before the upload, if any.

Correct me if I am wrong, but by not going in order, you are just trying to see if you have scanned the spreadsheet number. Could you just do this with a simple vlookup? If your above example is in columns A, B, and C, you would put

Please Login or Register  to view this content.

of course changing B100 to the farthest value down in that column.

gillry7, That is very nice. Thank you.

Of course as I play with the formula, I realize a few things that I need to do, but cannot figure out... again. Since my scanned input contains a unique date that my spreadsheet does not, the scanned info does not match exactly with my cell data already in my spreadsheet. How do I make the vlookup formula work (for example, match A2 with my scanned data+date) with not-exact lookup values? I tried a few different ways in vlookup without success.

Thank you all for the help!

Can you specify how they do not match up exactly? For example, are they different data types (one is a number, one is text), or one might contain extra values (xyz123 and xyz1234).

I think I just figured out one way to make it happen. What do you think about this, a modified version of your vlookup with fuzzy match "*"&H2&"*"... IFERROR(VLOOKUP("*"&H2&"*",I$2:I$100,1,FALSE),"--blank--")

To answer your how are they different question, the scanned value has the date of the scan after the barcode input, for example, xyz123 25July2014.

That will work as long as nothing is added before or after xyz123. For example, it will still show as a match for xyz1234 even though that might be a different part, but because xyz123 is in there, it shows. A solution for this could also be:

Please Login or Register  to view this content.

using Control + Shift + Enter to enter the formula as an array formula. It will have a 1 (or more if there are duplicates) if there is a match, and 0 if there is not a match.

Looks good so far. Thank you for the help. You certainly know quite a bit!

Originally Posted by QTS18

I think I just figured out one way to make it happen. What do you think about this, a modified version of your vlookup with fuzzy match "*"&H2&"*"... IFERROR(VLOOKUP("*"&H2&"*",I$2:I$100,1,FALSE),"--blank--")

To answer your how are they different question, the scanned value has the date of the scan after the barcode input, for example, xyz123 25July2014.

Using the quoted iferror(vlookup...) method above, how can I modify the formula to get the latest (most recent) date of my scan to appear? As it works now, I get the scan that was performed first, which is also in the lowest number row since each successive scan is automatically place in the subsequent row. As an example, my scanned input looks like this, "20PurpleRunner-06456 8/1/2014 10:02 PM", and maybe the next time I scan it appears as "20PurpleRunner-06456 8/3/2014 10:34 PM". I'd like to get the most recent scan to bump the older scan out.

I tried a few variations of sumproduct and max formulas without success. Thanks!

With the vlookup formula you will not be able to do that unless you can sort the data. Vlookup looks at the first match in the list and takes that value, so if you can sort by the date, then the normal vlookup formula you have will work. If not, you will need a different formula. Let me know if you cannot sort.

Wow, thanks again. I am unable to sort by date, so that requires a different formula? ...And just when I thought I met my requirement! Thanks for your help.

I guess this now depends on how your data is structured. If the newest dates are not at the top, are they at least at the bottom? For every new scan, I would hope that it gets added to the bottom of the list. If not, we will basically have to find every occurence, and then find the latest date of that occurence.

I am hoping that the most recent date will be bottom most occurence in your list.

I guess this now depends on how your data is structured. If the newest dates are not at the top, are they at least at the bottom? For every new scan, I would hope that it gets added to the bottom of the list. If not, we will basically have to find every occurence, and then find the latest date of that occurence.

I am hoping that the most recent date will be bottom most occurence in your list.

Yes, the most recent dates are added to the bottom.

There is a way to do a vlookup from the bottom to top using lookup instead of vlookup. However, I could not find a way to use your fuzzy much with that formula. The formula below will work though to get the bottom most value in the list with a match.

Please Login or Register  to view this content.

Using Ctr + Shift + Enter to enter as an array formula.

If there is no match, it will return B1 which I believe is "Scanned Inventory". If you want it as --blank-- use:

Please Login or Register  to view this content.

Again using Ctr + Shift + Enter.