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

1. ## 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!!!!

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

Hi,

Does the attached, (non macro solution) help?

3. ## 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. ## 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. ## 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.

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

Originally Posted by dwhite30518
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. ## 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. ## 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. ## 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.

10. ## 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!!!!

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

#### 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