+ Reply to Thread
Results 1 to 11 of 11

Letter Puzzle

  1. #1
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    17

    Letter Puzzle

    Hi All,

    I'm new to this forum, I'm from Ireland and need some help with an Excel problem. Hopefully the attached will explain clearly what I'm trying to achieve.

    Regards to everyone

    LETTER PUZZLE.png
    Attached Files Attached Files
    Last edited by martinoconnor; 08-05-2019 at 07:21 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    MS Office Excel 2007
    Posts
    6,662

    Re: Letter Puzzle

    "I need to change the values in C4 to C8 which will represent the letters in A4 to A8"

    There are no letters in A4 to A8, cells A4 to A8 are blank in the attached spreadsheet
    Amd anyway, it looks like you mean rows 5 to 9 not 4 to 8.

    Are you looking for a formula solution or VBA?
    Last edited by Special-K; 08-05-2019 at 07:24 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    17

    Re: Letter Puzzle

    Hi Special K,

    Apologies about the incorrect columns, you are correct in your understanding of my problem. I am looking for a formulae please

    Martin

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Letter Puzzle

    So, what are you trying to do? Enter a bunch of letters in F6 and H6, calculate the values that they represent and do a division?

    Will it always be EFAF/JEH... or can you enter any of the letters in any combination?

    If so, what is the maximum number of letters in any one cell?
    Glenn



  5. #5
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    17

    Re: Letter Puzzle

    Hi Glenn,

    In the particular puzzle which i want to solve they letters are what i have presented on this forum, however, future puzzles will have different letters and different amounts of letters in each cell.

    Martin

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,160

    Re: Letter Puzzle

    Please show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

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

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,418

    Re: Letter Puzzle

    I'd be surprised if there were a reasonable formula-based solution. I think you just need to ratchet through the permut(10, 5) permutations to find one that works.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    17

    Re: Letter Puzzle

    I think i may have found a solution, it may be a long way around and not to efficient so any modifications would be appreciated.

    I am not able to attach the spreadsheet because the attachment link wont activate, so again i have attached an image

    LETTER PUZZLE solution.png

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,160

    Re: Letter Puzzle

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,418

    Re: Letter Puzzle

    Here's a way to do it with a little bit of code (a three-line sub, a couple of user-defined functions, and a named formula):

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    0.836
    0.462
    0.378
    0.850
    0.139
    0.538
    0.868
    0.669
    0.506
    0.226
    A1: =RAND()
    2
    3
    A
    E
    F
    H
    J
    4
    2
    6
    7
    1
    9
    A4: =RANK(A1, $A$1:$J$1) - 1
    5
    6
    EFAF
    /
    JEH
    =
    F
    7
    8
    6727
    /
    961
    =
    7
    A8: =SUMPRODUCT(LOOKUP(MID(A6, RowVec(1, LEN(A6)), 1), $A$3:$E$3, $A$4:$E$4), 10 ^ RowVec(LEN(A6) - 1, 0, -1))
    9
    6727 / 961 = 7 K9: =Cat(A8:E8, " ")
    10
    TRUE
    K10: =Eval


    The numbers are assigned randomly and the worksheet calculated until the expression is true. Takes a few seconds.

    The letters in row 3 need to be in alphabetical order.
    Attached Files Attached Files
    Last edited by shg; 08-05-2019 at 02:23 PM.

  11. #11
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    17

    Re: Letter Puzzle

    Thank you all for your assistance in helping me to solve my query, your time and help is very much appreciated. I am satisfied with the results achieved.

    Regards

    Martin

+ 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