+ Reply to Thread
Results 1 to 10 of 10

Sorting & counting letters

  1. #1
    Registered User
    Join Date
    01-05-2013
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Sorting & counting letters

    Hi all,

    Apologies if this is the wrong area to post however my knowledge of excel is such that I don't know which subsection this problem would go in.

    So my dilemma is I make personalised products, chocolates and customers send me the message they want in chocolate and we produce and box it.. For example 'I LOVE YOU DAD XX' or 'HAPPY BIRTHDAY SARAH X' Each letter is made individually and then we spell out the message as we put the chocolates in to the box. As the number of orders we have has increased I wanted to fashion a way in which we can copy & paste all the messages we receive to make each day into excel and then, by some excel magic, have an output which tells us how many of each letter we need to produce each day. Eg using the above two example messages given earlier the output would tell us we need to produce 2x the letter I; 1x L; 2x O; 1x V; 1x E; 3x Y; 3x D; 5x A and so on..

    Does anyone know any way we could do this is excel? We currently do it manually with good old pen paper and tallys, I have thought of typing each letter in a separate cell then doing a query on the page as to how many of each letter were found (this is probably really showing my amateurish excel skills up now !). Anyway if anyone knows any way we could do it (and explain it in basic language!) I would be super grateful. Many thanks in advance!

    Rose

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Sorting & counting letters

    Is the attached workbook the sort of thing you're looking for?

    (I am happy to accept payment in the form of cakes )
    Attached Files Attached Files

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

    Re: Sorting & counting letters

    The attached file shows how you can do this. You can type your message in column A below A4 (doesn't matter if upper or lower case), and the adjacent cells show how many letters you need for that message. Keep typing new messages in column A and the totals for each letter will be shown on row 2.

    I've copied the formulae down to row 20 - copy further if needed.

    Hope this helps.

    Pete
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-05-2013
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Sorting & counting letters

    Awesome- thanks very much both! Either of your spreadsheets will do the trick so thank you both very much, will happily send you both a box of chocs if there's anyway way to send me your address on a message without sharing it with the world then please do. My sister is the boss who set me the task so I'm sure shed be more than happy to oblige with the choc provision! Thanks again! Rose

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Sorting & counting letters

    It's very kind of you to offer, but it was, literally, a minute's work to do what you needed, so there's really no need.

    Thank you anyway, glad you're sorted.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Sorting & counting letters

    Andrew and Pete, I was just looking at both of your very clever formulas, it was interesting to see the similarities and differences

    Pete =LEN($A4)-LEN(SUBSTITUTE(UPPER($A4),B$3,""))
    Andrew = {SUM(LEN($A$1:$A$100)-LEN(SUBSTITUTE(UPPER($A$1:$A$100),F1,"")))}

    Andrew's formula creates a running total in thew same cell, while Pete's uses a seperate cell for the running total

    both very clever and both very clean, congrats
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    01-05-2013
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Sorting & counting letters

    Sorry me again- final question to you both after I've just had a think... (for a large box of chocolates- wow that that only took you a minute but here's further conundrum that might make you feel you really deserve a sugar fix for your efforts!) is there anyway we could further segregate the results to differentiate between dark and white chocolate? So... customers can choose if they want their message written in dark chocolate or white chocolate so is there a way we could tag each message in the workbook as either dark or light (they can't mix! thank goodness!) so we would know how many white chocolate A's, B's etc and how many dark chocolate A's B's etc.. I was going to just have two pages of your formula and insert dark choc messages in one page and get total and then white chocolate messages in another page however since you guys managed to do my original query so fast I wonder if you know how I could do this all on one page as this would be much quicker and less likely to lead to mistakes being made.

    If you can figure a way to do that that would be fabulous. If you would like a further challenge (complication in my eyes!) we also have 4 options for non-letter chocolates we use fill spaces (hearts, daisies, cars or cupcakes) and we also need to calculate how many of these to make... so if I try and explain a bit better. Customers can choose if they want a box of 12 chocolates, or 25 chocolates, they then tell us their message, then tell us if they want in dark chocolate or white chocolate and finally they tell us which of the 4 options (hearts, daisies, cars or cupcakes) they want us to fill up spaces with- they can only choose one option (thank goodness again!). For example I LOVE YOU DAD XXX is 14 characters long so they will be getting a 25 box and therefore we will also need to make 11 of their chosen options to fill spaces e.g. hearts. If you two super excel wizards can tell/show me how we can incorporate all of that if possible on one sheet or if needs be two (say one page for 12 boxes, one page for 25 boxes).. honestly I will sort you a nice big box of chocs! (and be eternally grateful!)

    thanks again, Rose

  8. #8
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Sorting & counting letters

    Would something like the attached do you?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-05-2013
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Sorting & counting letters

    totally genius!! wow! honestly that is fabulous!

    Many many thanks!

    Rose

  10. #10
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Sorting & counting letters

    Not a problem, happy to help.

    The 'Size' column highlights in red if the message is longer than the number of chocolates selected, and if you ignore that it messes up the counts for the number of filler chocolates, but other than that it's reasonably robust.

    Glad you're sorted, anyway.

  11. #11
    Registered User
    Join Date
    01-05-2013
    Location
    Leeds, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Sorting & counting letters

    Andrew- have just tried to pm you so we can post you some chocs but your inbox is full! Will try again later if you've managed to clear it!

    Thanks loads again.

  12. #12
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Sorting & counting letters

    Not a problem, happy to help.

    The 'Size' column highlights in red if the message is longer than the number of chocolates selected, and if you ignore that it messes up the counts for the number of filler chocolates, but other than that it's reasonably robust.

    Glad you're sorted, anyway.

+ 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