Inventory Issues, counting multiple UPC codes and specifying location.

**forestavekids** · 10-06-2016, 08:39 PM

Hi all,

Having an issue with my inventory system.

I have products in bins labeled A through Z, we recently went through all bins and scanned the UPC codes in each bin.

I am trying to get a count of how many quantities of each UPC code we have, and where they are located.

There will definitely be duplicate UPC codes. And the same UPC code could be in bin A and bin K.

So I would like to know how many of each UPC code we have, and what bins there are in (especially if in multiple bins).

Please see attached, thank you for any/all help!

**FlameRetired** · 10-06-2016, 09:14 PM

The upload has a mix of numbers and text "numbers". If you are not aware of it they are not the same. It can make a difference in the formula options.

It is best to have them all the same ... I suspect numeric will work best. Do you have a preference?

**forestavekids** · 10-06-2016, 09:17 PM

Numeric is preferred.

**FlameRetired** · 10-06-2016, 10:18 PM

In Sheet2 find the converted numeric values. This is a named range in Name Manager ... UPC_codes. It was done by referencing the text numbers in Sheet1 with this entered in A2 of Sheet2 filled down and across

Formula:

Please Login or Register  to view this content.

I copied that range and pasted values back unto the range.

In Sheet3 A1 this formula counts the total number of UPC_codes.

Formula:

Please Login or Register  to view this content.

In B1 this formula counts the number of unique UPC_codes.

Formula:

Please Login or Register  to view this content.

In column A is a list of all UPC_codes ... duplicates and all. Formula is

Formula:

Please Login or Register  to view this content.

Column B is a helper column. It assigns a row number to the first occurrence of each UPC_code tagging them as unique. Formula is

Formula:

Please Login or Register  to view this content.

Column C is the unique extractions. Formula is

Formula:

Please Login or Register  to view this content.

The total count of each unique.

Formula:

Please Login or Register  to view this content.

Then in E3 filled down and across returns the row location under each Lettered Bin.

Formula:

Please Login or Register  to view this content.

**forestavekids** · 10-06-2016, 10:48 PM

[QUOTE=FlameRetired;4496957]In Sheet2 find the converted numeric values. This is a named range in Name Manager ... UPC_codes. It was done by referencing the text numbers in Sheet1 with this entered in A2 of Sheet2 filled down and across

Formula:

Please Login or Register  to view this content.

I copied that range and pasted values back unto the range.

In Sheet3 A1 this formula counts the total number of UPC_codes.

Formula:

Please Login or Register  to view this content.

In B1 this formula counts the number of unique UPC_codes.

Formula:

Please Login or Register  to view this content.

Flame, first off. Thank you very much for the work, that didn't look easy.

Secondly, I do not think the table is accurate. I re-attached, I cross referenced my original UPC list with bins. I only looked at bin H, where I have scanned 17 UPC codes. I bring this up because the table would show 46 codes in those bins.

Thinking there is a minor tweek that needs to be made.

**forestavekids** · 10-06-2016, 11:31 PM

Also, is there any way to get the bin location in a cell adjacent to the UPC code?

**FlameRetired** · 10-06-2016, 11:36 PM

My oversight on two counts.

I was working the wrong mission. I was locating the row numbers of first code under each bin letter.

When you said the same UPC could be in bin A and K it never occurred to me you wanted to account for multiples within each bin. I've changed that to the counts under each bin.

My apologies.

I'm still confused on this part.

Secondly, I do not think the table is accurate. I re-attached, I cross referenced my original UPC list with bins. I only looked at bin H, where I have scanned 17 UPC codes. I bring this up because the table would show 46 codes in those bins.

The original upload data is in Sheet1. I count 16 in bin H. I don't know what 46 codes you are referring to.

In the meantime I have made the following changes.

In creased the range of UPC_codes to
Formula:
```
Please Login or Register  to view this content.
```
Previously I had limited it to the data that was there.
In Sheet3 there is a summary of each bin in row 1.
In column D the new formula is
Formula:
```
Please Login or Register  to view this content.
```
In the counts range columns E:AD the new formula is
Formula:
```
Please Login or Register  to view this content.
```

Let me know if we're closer.

**forestavekids** · 10-06-2016, 11:39 PM

Flame, the 46 was referencing the sum of column H. If check back to the first sheet you posted its at the bottom.

This second sheet looks a lot better.

I want to take it on step further, is there any way to get it out of table, and in Column a1 have the UPC code, Column b1 quantity counted, Column c1 the bin it is located in?

**FlameRetired** · 10-06-2016, 11:42 PM

Originally Posted by forestavekids

Also, is there any way to get the bin location in a cell adjacent to the UPC code?

Probably. Let me think about that one. Where did you want those? What layout? It would be helpful if you would upload a small hand typed mock-up of what you envision.

In the meantime I await your feedback on the latest.

**forestavekids** · 10-06-2016, 11:42 PM

Flame, the 46 was referencing the sum of column H. If check back to the first sheet you posted its at the bottom.

This second sheet looks a lot better.

I want to take it on step further, is there any way to get it out of table, and in Column a1 have the UPC code, Column b1 quantity counted, Column c1 the bin it is located in?

**forestavekids** · 10-06-2016, 11:44 PM

Sorry check sheet 4.

**FlameRetired** · 10-06-2016, 11:51 PM

Flame, the 46 was referencing the sum of column H. If check back to the first sheet you posted its at the bottom.

Ahaa!! I see it, now. What you have there is a sum of the row numbers each code was located in ... a side-effect of my previous miss-interpret.

I want to take it on step further, is there any way to get it out of table, and in Column a1 have the UPC code, Column b1 quantity counted, Column c1 the bin it is located in?

There are multiple bins for many of the codes. Can we do it across column c1:f1 if need be? Or were you wanting to concatenate all of them into one cell per code? If it's the latter are you open to some VBA coding? Concatenation can be a little tricky by formula.

**forestavekids** · 10-07-2016, 12:29 AM

It would have to be in a single cell as I am uploading it via CSV to my inventory system. Kinda my first stab at this "bin" system. Also kind of late I chucked all these loose articles in bins not thinking I could have a UPC code in multiple bins.

**FlameRetired** · 10-07-2016, 12:38 AM

Sorry. Caught your post #11 after I posted. Couldn't respond. Succurri was acting up, again. It happens.

I believe that can be done with some VBA ... a user defined function if you're OK with that.

I'll work on it with that in mind.

**forestavekids** · 10-07-2016, 12:40 AM

Originally Posted by FlameRetired

Sorry. Caught your post #11 after I posted. Couldn't respond. Succurri was acting up, again. It happens.

I believe that can be done with some VBA ... a user defined function if you're OK with that.

I'll work on it with that in mind.

Pretty much a learning experience up to this point anyway lol.

**FlameRetired** · 10-07-2016, 01:08 AM

This is a macro enabled file. It contains code for a user defined function called CONCATALL. For future reference you can find the code on the forum here. You simply copy the code, open the VBA editor (Alt + F11), click on your file in the Project Explorer, open a standard code module and paste the code into that.

http://www.excelforum.com/tips-and-t...ml#post3096647

Sheet4 is a cut down version of Sheet3. It saved some work.

The unique codes are in column A. It's the same formula but with references to Sheet3. Ditto column B.

In column C this array entered formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

**forestavekids** · 10-07-2016, 12:24 PM

Man looks great. I am really going to back track your work, and lock some of these formulas in so I can use this in the future.

I have 1 more thing to ask... If I want to change the bin names, it throws off an error. I wanted to change them to A1, A2 ect... I changed the bin name in each sheet, but no luck.

**FlameRetired** · 10-07-2016, 01:10 PM

You will need to change those bin names in all affected sheets.

IPO I would change them all in Sheet2 first (the converted to numeric sheet).
Copy that range of names.
Paste that into each of the other sheets replacing what is currently there.
Everything should still work and the new bins refs will show up in the locations column.

Please let me know how it goes.

**forestavekids** · 10-07-2016, 01:44 PM

Originally Posted by FlameRetired

You will need to change those bin names in all affected sheets.

IPO I would change them all in Sheet2 first (the converted to numeric sheet).
Copy that range of names.
Paste that into each of the other sheets replacing what is currently there.
Everything should still work and the new bins refs will show up in the locations column.

Please let me know how it goes.

I did as said, changed bin names in sheet 2, and copied the entire bin row into all the other sheets.

They all update except for sheet 4, which throws off the #name error in the bin column.

**FlameRetired** · 10-07-2016, 03:51 PM

I forgot to mention something else.

Excel considers this a macro file (*.xlsm). These must be stored in a location designated as "Trusted Locations".

You will likely want to create a new sub-folder under Documents or wherever you keep spreadsheet files.
Save the *.xlsm file to that location.
Close the file.
Go to File > Options > Trust Center > click Trust Center Settings... > Trusted Locations > Add new location... and select the new sub-folder location > check any other options you like.
Click OK all the way back out.
Open the file from the new sub-folder.

Sorry for the oversight.

**forestavekids** · 10-07-2016, 04:49 PM

Couldn't thank you enough!! Thank you!

**FlameRetired** · 10-07-2016, 05:54 PM

You are welcome. Thank you for the feedback and the rep.

**forestavekids** · 10-20-2016, 11:04 PM

Originally Posted by FlameRetired

You are welcome. Thank you for the feedback and the rep.

Flame, having some issues with this again.

I deleted all the UPC codes, and re-scanned a whole bunch more. I also changed the bin names, I copied both from sheet 2, to sheet 1.

I can't seem to get the UPC codes & count to work on sheet 3 and 4. I also can't get the bin names to change.

Any chance you can take a look?

**FlameRetired** · 10-20-2016, 11:24 PM

The named range UPC_codes only went out to column Z in the initial solution (Post #16). You will need to extend that to the new range.

To see what I mean select Sheet4 cell D3. In the formula bar double click UPC_codes.

That will select it.
Copy that name (Ctrl +C).
Escape.
Hit the F5 function key.
Paste (Ctrl + V).
Hit enter.
That will select the current range UPC_codes.

When you redefine the named range make sure to also include enough rows in the new definition. You will find that definition in Name Manager where you can edit it.

You will also need to update the range of the header names in this part of the D3 formula (filled down and across). It too currently ends in column Z.

MATCH(D$2,Sheet2!$A$1:$Z$1,0)

**forestavekids** · 10-20-2016, 11:35 PM

Originally Posted by FlameRetired

The named range UPC_codes only went out to column Z in the initial solution (Post #16). You will need to extend that to the new range.

To see what I mean select Sheet4 cell D3. In the formula bar double click UPC_codes.

That will select it.
Copy that name (Ctrl +C).
Escape.
Hit the F5 function key.
Paste (Ctrl + V).
Hit enter.
That will select the current range UPC_codes.

When you redefine the named range make sure to also include enough rows in the new definition. You will find that definition in Name Manager where you can edit it.

You will also need to update the range of the header names in this part of the D3 formula (filled down and across). It too currently ends in column Z.

MATCH(D$2,Sheet2!$A$1:$Z$1,0)

Alright, extended the range.

Though there are no new UPC in column A on sheet 4.

I feel like there are a whole bunch of UPC not in the sheet 4, column A list.

I should have somewhere close to 1000 individual codes scanned.

**FlameRetired** · 10-21-2016, 02:03 AM

I've reworked the entire workbook. Yes. There are 937 individual codes ... 232 uniques.

The changes are too extensive to describe in detail here. I am going to do something I try never to do and that is to upload the reworked file with little to no comment or formula post. It's a rework anyway.

This file is an extended version of the file in Post #16. The named range is changed. The ranges in the formulas are changed to reflect that. The formulas are filled down and across in both Sheets 3 and 4 to accommodate the increase in data.

I would encourage you to dissect these formulas and understand them. It sounds like there is going to be some inconvenient editing in the future. Normally I would overcome this by exaggerating the ranges for future data growth and extending the formulas well beyond what is needed. I was reluctant to do that. This file is already getting rather large.

That might be less impractical at your end.

**forestavekids** · 11-26-2016, 11:47 PM

Flame,

Any chance you can take a look at this again? I had to change bins name, and scanned all new UPC's.

Everything seems to add up except the bin names on sheet 4.

Please let me know, thank you.

**SPARKY347** · 11-27-2016, 01:16 AM

I'm No expert by any means, and I just briefly browsed through this on my iPhone before bed. But this really seems like something that should be entirely written in VBA due to the fact that the bin names and the size of you data is constantly changing. I could attempt it but it may take a while

**forestavekids** · 11-27-2016, 01:28 AM

Hi Sparky I would definitely appreciate it. I'd like to use this as a template moving forward.

Lets see if Flame comes up with something as well.

**FlameRetired** · 11-27-2016, 02:43 PM

forestavekids,

I concur with SPARKY347. I believe a VBA solution would be far more practical and efficient than what I have started.

There are too many dynamic changes for me to keep up with, and I believe writing practical formula solutions here would be out of my reach. Even then I would have to do a complete rework of my previous approaches. They would not likely fare any better.

Thank you for posting this one. It's an interesting and challenging problem.

Regards,
Dave

**SPARKY347** · 11-27-2016, 07:43 PM

I'll be a while before I can stab at this but at least VBA will be able to adapt to the changes in bin numbers and ranges. Can I suggest posting the VBA section of the forum with a link to this thread. And maybe re type exactly how you want our calculations to work? The VBA. Pros can work much faster then me

**forestavekids** · 11-27-2016, 08:44 PM

Yep go for it!!

**SPARKY347** · 11-27-2016, 10:01 PM

No no no you go there and ask for help

**forestavekids** · 11-28-2016, 12:36 AM

So I figured a bit of a work around..

I didn't rename the bins, only deleted and added new UPC's.

I lined up my "new" on another sheet, for instance dunellen 1 was replaced by bin "w2". I manually did a find/replace of "dunellen 1" with "w2".

Is there script you guys can write so that I could put Dunellen bins in column A, and new bins and column B and it would auto find/replace all of this? See attached....

**SPARKY347** · 11-28-2016, 10:19 PM

quick question, on your newest workbook you have the bins labled in A1 - Dunellen 1 and A2- W2 will you be using both bin names or just the one name?

**forestavekids** · 11-28-2016, 10:59 PM

Originally Posted by SPARKY347

quick question, on your newest workbook you have the bins labled in A1 - Dunellen 1 and A2- W2 will you be using both bin names or just the one name?

Just one name. So row 1. Though the bin names will always change, as will the UPC codes... Thats why I work off the sheet with "dunellen" on it, and placed the "new" bins name underneath and find/replaced them all. A bit complicated.

**SPARKY347** · 11-29-2016, 12:49 AM

So dunellen will always be there? I just need to know what row to pull the bin names from so row 1? If you have the second names in row do. Does and will the data always start in row 3? You have to nail down a format for me

That being said I started today and had It so it would dynamically pick up and subtract any items you have. And started working on the bin matrix when excel crashed. So I'll start again tomorrow

**forestavekids** · 11-29-2016, 12:56 AM

Hey Sparky take a look now, let me know if you have questions.

Thanks again.

**SPARKY347** · 11-29-2016, 08:04 PM

just looked at your data you need to manually fill in some of the data onto sheet 3 and sheet 4 as they all just say NA and REF, I'm not back tracking the formulas to find out what the data is your trying to display, can you also rename the sheets to the names you would like them to be?( please do not change the sheet names once I code this)

**SPARKY347** · 11-29-2016, 08:12 PM

also whats the difference between sheet 3 and sheet 4, also sheet 3 column B what are you looking with "1st of uniques"

**SPARKY347** · 11-30-2016, 01:14 AM

so i was having trouble attaching files, spent a bit this evening coding a bit. so far everytime you make a change or an input on the "bin UPC' sheet it triggers the code to start running, the code is a bit glitchy and does take a bit of time due to the amount of times you need to apply the advanced filter and remove, i think in the future i will just add an "update" button so you don't have to wait for the code evertime you make a change. currently i have it to summerize all your upc's
then get all the individual codes, then go through all individual code for a total count of each code. ill work on the rest in abit

Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Re: Inventory Issues, counting multiple UPC codes and specifying location.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Getting issues in combining two codes with many multiple actions

[SOLVED] How list inventory item codes so that they will be sorted correctl

Bookmarks

Bookmarks

Posting Permissions