+ Reply to Thread
Results 1 to 19 of 19

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

  1. #1
    Registered User
    Join Date
    05-03-2020
    Location
    Lexington, Kentucky
    MS-Off Ver
    Latest
    Posts
    7

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,331

    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
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,331

    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
    Attached Files Attached Files
    Last edited by Keebellah; 05-25-2020 at 02:36 AM. Reason: attachments added

  4. #4
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    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. #5
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    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. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,331

    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. #7
    Registered User
    Join Date
    05-03-2020
    Location
    Lexington, Kentucky
    MS-Off Ver
    Latest
    Posts
    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. #8
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

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

    Quote Originally Posted by Keebellah View Post
    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.
    Last edited by Metaldrgn; 05-25-2020 at 06:16 PM.

  9. #9
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

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

    Quote Originally Posted by LucasSp View Post
    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. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,331

    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. #11
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    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.
    Last edited by Metaldrgn; 05-25-2020 at 07:05 PM.

  12. #12
    Registered User
    Join Date
    05-03-2020
    Location
    Lexington, Kentucky
    MS-Off Ver
    Latest
    Posts
    7

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

    Quote Originally Posted by Keebellah View Post
    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.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-03-2020
    Location
    Lexington, Kentucky
    MS-Off Ver
    Latest
    Posts
    7

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

    Quote Originally Posted by Metaldrgn View Post
    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. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,886

    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. #15
    Registered User
    Join Date
    05-03-2020
    Location
    Lexington, Kentucky
    MS-Off Ver
    Latest
    Posts
    7

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

    Quote Originally Posted by jindon View Post
    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?
    Last edited by LucasSp; 05-25-2020 at 10:15 PM.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,886

    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"

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,886

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

    If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.

  18. #18
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

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

    Quote Originally Posted by LucasSp View Post
    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:
    Didn't realize the question was already answered about the sumproduct

    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.
    Last edited by Metaldrgn; 05-26-2020 at 01:55 PM.

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,331

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

    Hi Jindon, great formula (as always)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 11-12-2018, 04:20 PM
  2. Count words and characters of hyperlinked word documents?
    By asterixo500 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-19-2015, 12:39 PM
  3. unique count beginning with or using zz* wild card
    By bharathsd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2013, 02:37 AM
  4. Replies: 2
    Last Post: 02-11-2012, 09:33 PM
  5. Count unique words appearing in a column once per row
    By eq2 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-19-2010, 12:09 PM
  6. count quantity of each unique value
    By Mungyun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2008, 12:29 PM
  7. Count unique characters in a string
    By yelkus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2007, 11:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1