+ Reply to Thread
Results 1 to 10 of 10

Want to count the number of times a particular letter appears within a range

  1. #1
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Question Want to count the number of times a particular letter appears within a range

    Good evening all!

    I have a range of data that contains text in column A and numbers in column B. I have attached a copy of the data set I am working with.

    I am using this as a form to know how many labels I will need to print for each letter. So I not only need to know the count of each letter in each cell with that range but I also will need to take that number and multiply it by the qty associated with it in column B.

    Ex. Cell A2 has the Contents...AM/AN. So there are 2 "A's", 1 "M" and 1"N". Then in column B the quantity shows 6 so I would take each of these values and multiply by 6 so I know how many labels I need for each letter in that cell and do that same for each cell in that range. I would then need to create either a list or a table that sums all of these values for each letter and shows me how many I need to have printed.

    I am not shy about using VBA when I can and if it would make it easier and faster to run this process, then I would love to learn how to do that. I am familiar with associating a macro to an action button and running the macro and would like to do that with this.

    Please let me know if you have any questions and I look forward to seeing your ideas!!!!
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Want to count the number of times a particular letter appears within a range

    Hi,

    Does the attached, (non macro solution) help?
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Want to count the number of times a particular letter appears within a range

    Take a look at this thread from the weekend:

    http://www.excelforum.com/excel-gene...html?p=3069763

    It's a very similar requirement to yours, and the solutions can work in a similar way (without macros) - you just need to multiply by the number of labels.

    Hope this helps.

    Pete

  4. #4
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Want to count the number of times a particular letter appears within a range

    Is there a way to do this counting without creating a table?

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Want to count the number of times a particular letter appears within a range

    Hi dwhite30518,

    This is a very hard problem without a table. See the answer I think you want without such a large table. No VBA required but you will need an Array formula to do it.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Want to count the number of times a particular letter appears within a range

    Quote Originally Posted by dwhite30518 View Post
    Is there a way to do this counting without creating a table?
    Most probably. But what have you got against a table using standard Excel functions?
    You can always isolate it on it's own hidden sheet for presentation purposes. As a general rule you should always use standard Excel functionality rather than reverting to VBA wherever possible. It's always quicker and more efficient and readable. If your data does vary in size then a half way house is to use VBA to build what you would otherwise do manually.

  7. #7
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Want to count the number of times a particular letter appears within a range

    I rather use a formula than VBA whenever I can. I guess I was just thinking the VBA code would be quicker than creating or altering the formulas.

    Now, what if my customer gives me a workbook with multiple sheets containing this data. I would rather not have to copy and paste the data to a "Master sheet" and then do these calculations. Is there a way to use these formulas to gather this information from all worksheets in the workbook?

    Maybe this is where a macro comes in to copy the data to a "Master sheet" (which I will hide) and then the formulas do the rest of the work.

  8. #8
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Re: Want to count the number of times a particular letter appears within a range

    Where the ranges are in the formulas, you would need to reference the sheet name you want to look at. For example, you would change A1 to Sheet1!A1 and so on. I'm not sure how experienced you are with VBA, but you can copy your data from a sheet to another sheet using this line of code:

    Please Login or Register  to view this content.
    You could set up a macro to copy everything to the master sheet this way, obviously, you'll need to adjust to suit your needs.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Want to count the number of times a particular letter appears within a range

    Hi,

    Here is a VBA answer for your question. I did some VBA functions to count the number of a letter in a word, then that number times the Qty on a single sheet and then for all sheets. I've left them as functions and used them in the attached. I hope this will let you study and learn how this can be done using VBA Code.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Want to count the number of times a particular letter appears within a range

    Thanks to all for your help!! I was able to use a combination of VBA coding as well as these formulas.

    Again....THANKS!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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