Need help I'm using scan gun. have three columns one column scans in the secont column a list of number the third column matches 1 and 2 and counts to the 3rd colum. I have that part working fine. New scaner gun scan and you type the amont in. Ok that places a comma after the code witch i used a mid formula that pulls that number over to the next coumn. I'm trying to add that number to the 3rd column???

To count reoccurring numbers and extract after comma and add that number.

I made a inventory sheet work nice put it just count one beep at a time. 



Selected Answer

You will need to add a helper column. This column must contain a formula to extract the quantity. You can hide it. This is the formula it should contain.


Note that the 6 in the formula expresses the number of characters to be extracted. The presumption is that nothing follows the number you want. Excel will ignore the instruction to extract non-existant characters but if you have very large numbers, like 123456789, the final digits will be truncated.

The formula modifies the result in two ways. One, an error wil occur if no comma is found. In that case the quantity will be presumed to be 1. Two, the result is multiplied with 1. This is necessary because the extracted part of A2 is a string (caused by the formula, irrespective of cell formatting) which can't be used in calculations. Multiplying it with 1 forces Excel to convert it to a number.

I added a column B in your Scan-Out sheet (same for the Scan-In). Note that it is necessary to format this column as "General". Throughout your workbook cells are formatted as "Text". I recommend changing that. If you don't know what to format a cell as, your first choice should be "General" which will cause Excel to determine the most suitable format based on what you enter.

Now that you have the quantities you want to add up in column B, sum them up in column E (that was column D before inserting the extra column B) using the formula below.


Note that the format in column E must also be "General" or a number format, not "Text".

I recommend that you review all your formulas. Formulas like =COUNTIF($A$2:$A$199,$D$2:$D$200), with differently sized ranges, will cause errors. Formulas like =(INVENTORY!A2:A182) are all but useless. Observe that it changes to =(INVENTORY!A2:A183) in the row below and =(INVENTORY!A202:A382) in the table's last row. Frankly, I don't know how it works and would appreciate your teaching me. However, it probably must be =(INVENTORY!$A$2:$A$183), with dollar signs to prevent the range from shifting when copied down.

Your workbook contains a lot of tables, some of them deleted but not removed. Look at the Name Manager (Formulas tab). Consider removing all tables. Tables are helpful for some advanced tasks. I feel that you might actually be better off without them. They just add rules to the ways you must work.

Finally, I saw that the code in your workbook is highly questionable. I tried to improve it but gave up. In principle, you tried to create tables with formulas copied down as far as you think you might need. This behaviour is anti-automation. It is designed not to require automation. Therefore automation will have difficulty working with it. If you wish for more automation in the future, get rid of everything that makes it unnecessary.



Thank you for your help. That code works but that the number i am trying to add.
Not sure how to attach a file to this. column 1 has 5000 codes running down column 2 has 100 codes so i match the codes between 1 and 2 and add to column 3 all that work fine. I used that formula to pull the end number and put in column 4 but i can't add them to column 3 for a total.  ( 122  +  122 = ( 1  ) 
( 122,12  + 122  =  (1)  + (12 ) the 12 is not on the same roll as the match code 122 but i'm trying to add 12 and 1 for 13.
Kenny (rep: 2) Sep 28, '18 at 9:51 pm
Kenny (rep: 2) Sep 28, '18 at 10:07 pm
I greatly apprecaiate you taking the time to walk me through this I'll work on it using the information you give me.
Kenny (rep: 2) Sep 29, '18 at 11:58 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login