# Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quantity

1. ## Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quantity

Hello, recently I have been working to make an automated excel inventory sheet using VBA. I am trying to find a way to perform a task currently that I can't figure out. I have an excel sheet set up where a quantity is counted of certain barcodes each that have a unique bin code at the start as a way of organizing them in certain bins. I tried using the countif function but it was limited and did what I needed and count all of the items with the same unique barcode to give an amount of items in a bin but I learned that won't work anymore because I can't find a way to get it to also add the quantity of each of those items. There may be a better way of doing this with functions instead of VBA and if so I will post there instead but sadly I struggle to find a lot of the information with specific tasks related to excel. Attached is an example workbook with the macros that have already been written and the countif functions to help demonstrate the issue I am trying to solve. I have included an example of the flaw for this as well.

Sorry if there is any confusion about this and I can try to clarify any of it. Thank you for any help given.

2. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Hi in Column D

``Please Login or Register  to view this content.``
this part of the formula counts all where the first 5 characters on the left match
``Please Login or Register  to view this content.``
The result is 11

``Please Login or Register  to view this content.``
The result is 75

Hope it helps

3. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Sorry of course the formula should go in column H

4. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

So I'm trying to understand what you want to do exactly. It looks like your VBA code works, but you just want to be able to put a quantity with the serial #?

5. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Ok I used an index match to do it.
``Please Login or Register  to view this content.``
or if you wanted the if error to be specific to the match not finding it:
``Please Login or Register  to view this content.``

6. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

What's wrong with COUNTIFS? it does the job and is less complicated, you can even set it ti match any number of characters left mid or right

7. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Sorry if it was confusing it is hard to explain. Each barcode has a tag at the start that signifies where the item belongs. (Literal tubs in a storage room). I use the countif to count the amount of items in the list of barcodes that belong in the bin to give me a total. This doesn't work because it needs to also count the quantity of the items with it as well because I can have multiple of the same item and it would only be counted as one.

8. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Originally Posted by Keebellah
What's wrong with COUNTIFS? it does the job and is less complicated, you can even set it ti match any number of characters left mid or right
I don't think COUNTIFS would work for this. COUNTIFS doesn't return values, it just counts if the conditions are met. You wouldn't be able to get the anything from the quantity column.

9. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Originally Posted by LucasSp
Sorry if it was confusing it is hard to explain. Each barcode has a tag at the start that signifies where the item belongs. (Literal tubs in a storage room). I use the countif to count the amount of items in the list of barcodes that belong in the bin to give me a total. This doesn't work because it needs to also count the quantity of the items with it as well because I can have multiple of the same item and it would only be counted as one.
So you have 3 sheets and they all vary a little. Is it just sheet1 (5) that you are working on?

Did you try our codes? We need feedback if these are not the answer.

10. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

I'll hook up again once I really understand what the OP wants.
My suggestion: add the sheet with the situation as is and another with the desired result, tells us more than words

11. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

if you have multiple things in each bin it's better to do it with VBA. Add this sub to your sheet and call it before the end of the Worksheet_Change sub.
``Please Login or Register  to view this content.``

12. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Originally Posted by Keebellah
I'll hook up again once I really understand what the OP wants.
My suggestion: add the sheet with the situation as is and another with the desired result, tells us more than words
Sorry for seeming inactive been working hard on a project. Attached is a worksheet with the current progress on sheet 1 and sheet 2 is the desired results. I have tried the first functions and none worked as intended either. I can see a flaw in using the countif and know it won't work. I am currently trying to test the code left by another user and see if it works. I'll be more active on this thread. Thank you for your desire to help with this.

13. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Originally Posted by Metaldrgn
if you have multiple things in each bin it's better to do it with VBA. Add this sub to your sheet and call it before the end of the Worksheet_Change sub.
``Please Login or Register  to view this content.``
Hello, I am confused on how to go about implementing this code. I am fairly new to VBA and have had lots of help along the way figuring out code and errors. I have added it before the end of the worksheet_change sub which ran an error. I have also added it at the very bottom of the code which causes no error for me but doesn't seem to do much. Sorry for my confusion. Thank you for your help so far.

14. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Not sure but...
Formula in H7

=SUMPRODUCT((TRIM(LEFT(\$C\$8:\$C\$100,6))=MID(G7,5,6))*(\$D\$8:\$D\$100))

Then copy down..

15. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Originally Posted by jindon
Not sure but...
Formula in H7

=SUMPRODUCT((TRIM(LEFT(\$C\$8:\$C\$100,6))=MID(G7,5,6))*(\$D\$8:\$D\$100))

Then copy down..
Well after all of this confusion this actually worked really well for what I was trying to accomplish. I don't really understand how to formula works though could you take a few moments to explain it to me? Just curious on the way it functions. Also say I wanted to add another column that adds for another bin such as bin B - 1 and so on. Would this formula also work for that?

16. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

1) Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

2) Sum col.D when 5 or 6 characters from the Left of col.C = characters of Middle fro 5.
e.g
A - 1 12345 Trim(Left(C8,6)) = "A - 1"
Bin A - 1 Mid(G7,5,6) = "A - 1"

18. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Originally Posted by LucasSp
Well after all of this confusion this actually worked really well for what I was trying to accomplish. I don't really understand how to formula works though could you take a few moments to explain it to me? Just curious on the way it functions. Also say I wanted to add another column that adds for another bin such as bin B - 1 and so on. Would this formula also work for that?
Edit:

I didn't even think of using sumproduct. If you want to see how it works, you can step through it under the formula tab click evaluate formula. Essentially it checks each the first 6 characters (a space is a character) against the 6 characters starting after bin. It returns an array of true/false results (which convert to 1 and 0 respectively. This is different in VBA) The *(\$D\$8:\$D\$100) creates an array from D8 to D100 w/ the values inside and multiplies each row against the true or false and adds them at the end. So if it's false, it would output 0 and 0 * x is 0 and if it's 1 it just returns the value from D because 1 * x would just be x.

As for my code I put earlier, you would add the code below the doDate subroutine in the sheet1 (5) worksheet code. Then add the call to that new sub between the following like :
``Please Login or Register  to view this content.``

19. ## Re: Looking For Way to Count Words With Unique Beginning Characters and Then Add the Quant

Hi Jindon, great formula (as always)

There are currently 1 users browsing this thread. (0 members and 1 guests)