+ Reply to Thread
Results 1 to 35 of 35

Counting letters in Strings.

  1. #1
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Counting letters in Strings.

    Here is a challenge for the experts I believe.

    Data:

    I have different strings S1: ABCF; S2: DABAF; S3: BACF

    Then I have a different string B1: DABACF. You can notice that string B1 contains all the letters from string S1, S2 and S3.

    What I need to do is take the first strings (S1, S2, S3) and count the moves in string B1. The starting point in B1 would be letter D (1) and the finishing point letter F.

    For example S1: ABCF B1: DABACF
    TO count the moves of S1 in B1 I would count the moves as followed. Moves: (LocationLetter - PreviousLocation)+ OldMoves

    Location Letter A of S1 in string B1 is 2. Moves: (2-0) +0= 2. OldMoves= Moves. PreviousLocation= LocationLetter
    Location Letter B of S1 in string B1 is 3. Moves: (3-2)+2= 3. OldMoves= Moves. PreviousLocation= LocationLetter
    Location Letter C of S1 in string B1 is 5. Moves: (5-3)+3= 5. OldMoves= Moves. PreviousLocation= LocationLetter
    Location Letter F of S1 in string B1 is 6. Moves: (6-5)+5= 6.

    That means the total moves to go from left to right were 6 for S1.

    If you do the same with S2: DABAF . The total moves you will also get 6.


    In the previous 2 examples even though we have repeated letters in B1:DABACF I only considered the first one that appears from left to right. If I would have selected the second letter it would have given me more moves and that is something I do not want. I would like to be able to recognize the position of repeated letters and also performed the calculation and at the end select the min.

    In the third example S3: BACF notice that to make the less amount of moves through B1: DABACF it would better to select the second A instead of the first one.

    In conclusion what I am trying to figure out is the minimum amount of "moves" for strings (S1, S2, S3) to go through another string (B1) taking into account repeated characters.

    Let me know if you come with an idea.

    Thank you,
    Maria

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Counting letters in Strings.

    It outputs the counts in column T of the same row

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Counting letters in Strings.

    "That means the total moves to go from left to right were 6 for S1." Can you explain from where the value 6 is coming from
    Does it make some difference to change formula from
    (LocationLetter - PreviousLocation)+ OldMoves
    to
    LocationLetter
    - Battle without fear gives no glory - Just try

  4. #4
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    stnkynts,

    I tried your code
    All of the results in the cases I gave you give me 6 moves. When I plugged another S4= ACBF using the same B1= DABACF I am getting 6. Instead I should be getting 10 moves.

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Counting letters in Strings.

    All of the results in the cases I gave you give me 6 moves. When I plugged another S4= ACBF using the same B1= DABACF I am getting 6. Instead I should be getting 10 moves
    I see now that this is going to be quite more complex than I originally thought. I wish you the best on finding a solution to your answer.

  6. #6
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Thank you stnkynts.

    Lets see if someone will come with an answer.

  7. #7
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,672

    Re: Counting letters in Strings.

    I'm with PCI - it seems like it is just LocationLetter. Show us the steps how S3 is calculated using the first A vs the second A.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  8. #8
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Pauleyb,

    That is the code that I am trying to figure out. If i don't have repeated characters I have no problems since I have one location for each character. I want to be able to find the position of the repeated characters as well and perform the calculations of the "moves" and figure out the min amount of "moves". That means the min amount of moves starting at the left side of the string B1, in this case character "D" and finishing at the right side of the string with character "F".

    For example the case S3: BACF to go though B1: DABACF

    You can notice that for this case it is better if the second A is used. Otherwise, a backward move will be made to go from B to the first A, giving me more moves than when using the second A.

    Let me know if you have any ideas.

    Maria

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

    Re: Counting letters in Strings.

    Can you share the purpose of what you're trying to do?
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Sure I will do my best.

    So I have a truck that has to load materials. The materials should be loaded in a certain order. In this case S1: ABCF; S2: DABAF; S3: BACF. The materials are represented by each character (ABCDF). The materials are arrange in a yard in the following order B1: DABACF. That means that first you have a bin with the material D, then a bin of material A, then a bin of material B, then another bin of material A, an so on until material F.

    I need to count the trucks moves for each load S1, S2 and S3 but it should be done in the most efficient way. That means having the least amount of moves. Avoiding to go backwards if is not needed. The starting point would be letter D and the finishing point letter F.

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

    Re: Counting letters in Strings.

    Thank you for that, almost there. S's are required load orders, and B's are bin orders, correct?

    Can you explain the physical process by which the truck loads?

    Maybe this: The truck pulls up alongside the first item it needs, then forward or backward to the next, until done?

    EDIT: Is there only one bin order? Or do you want to optimize bin order for several loads?

    Is this the extent of the problem, or is it actually more complicated?
    Last edited by shg; 06-03-2015 at 11:10 AM.

  12. #12
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Ok.

    So Ss are load orders and B would be the bins correct.

    The physical process would be to drive to the bin where the first material to load is located. I am not considering the loading process itself because it would be the same for all. Then, drive to the next bin where the next material to load is located, and so on until the load order is completed.


    I started the problem finding the location of the material of S in B and calculation the moves as followed;

    moves= (locationMaterial - oldLocation)+ OldMoves

    And doing a loop for S load until all characters were completed.

    The problem arose when I have repeated materials and vba always look for the first location.

  13. #13
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    What I need to minimize is the amount of moves made by the truck for each load.

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

    Re: Counting letters in Strings.

    The total distance of moves, no? It has to move once for each item it loads, so the number of moves is constant.

    What's the relative cost of moving forward versus back? Is it better to move forward 2 than back 1? Forward 3 than back 1?

    And again, is this the actual complexity of the problem, or can there be more bins and more items on the truck? What's the practical upper bound of each?

  15. #15
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Yes, I am not measuring the distance itself.

    I am counting each move depending on the position of the material. For example in B1: DABACF. If we are starting and you want to go to material D. It would take one move to get there since it is the first bin. Or if we are starting and want to get to material B, it would take you three moves. Then if you want to go from B to C that would be a total of two moves. At the end I count the amount of moves. For example for S1: ABCF it would me a total of (2+1+2+1) = 6 moves. So the moves are not necessary constant it depends on how the materials are organized.

    What's the relative cost of moving forward versus back? Is it better to move forward 2 than back 1? Forward 3 than back 1?
    I am included the character F at the end in both S Loads and order of the bins since I want that to be my final point.


    In the case S3: BACF; B1: DABACF. It would be better if the truck drives to B and then to the second A instead of driving backwards to the first A since it will take him more moves to go from the first A to C, that from the second A to C.

    And again, is this the actual complexity of the problem, or can there be more bins and more items on the truck? What's the practical upper bound of each?
    Actually I can have many more loads options (around 5). They could be longer, but not more that 7 or 8 materials. Sometimes the material could be repeated e.g S2: DABAF.

    So what I have coded is that I tell the program the loads Ss that I would need. Then the program takes this loads, include all the material needed for the loads and performed permutations. This will give me all the options of Bs (Bins orders). (I already coded this part and it prints in my excel sheet). So I have all my Ss (which were given my me the user) and the program calculated all my possible Bs.

    Now, with that information I want to evaluate each Ss in all the Bs options. Calculating the moves that it takes for each S in each B will help me determined which is the best layout of the bin Bs for the loads Ss plugged by the user. Here is where I am stuck with the coding!

    I hope you can help me solve this.

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

    Re: Counting letters in Strings.

    Again ,

    What's the relative cost of moving forward versus back? Is it better to move forward 2 than back 1? Forward 3 than back 1?

  17. #17
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Well it depends.

    Yes, it will always be better if you move forward unless you do not have any options to go forward and have to move backwards to find the material.

    I dont know if for you better is a greater or a lower number.

    How I was doing was looking for the min number of moves. That means that it is better for me to have lower number.

    It would have a relative cost of 1 for moving forward and 2 for moving backwards.

  18. #18
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,672

    Re: Counting letters in Strings.

    So, I am beginning to think your example algorithm is wrong. Is that a fair statement? Using the algorithm below, please show me the calculations for B1 and S3 when using the first A and when using the second A. With your other text, I think I better understand your goal, but I want to confirm we are seeing/calculating the same thing.
    Quote Originally Posted by salasmau View Post
    Here is a challenge for the experts I believe.

    Data:
    ... S3: BACF
    ... B1: DABACF.

    What I need to do is take the first strings (S1, S2, S3) and count the moves in string B1. The starting point in B1 would be letter D (1) and the finishing point letter F.

    TO count the moves of S1 in B1 I would count the moves as followed. Moves: (LocationLetter - PreviousLocation)+ OldMoves

    In the third example S3: BACF notice that to make the less amount of moves through B1: DABACF it would better to select the second A instead of the first one.

  19. #19
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Yes, that algorithm worked for when I dont have repeated characters. But it gives me a wrong answer when I have repeated characters since it always look for the first letter.

    So I want to find the right algorithm that work for all the cases.

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

    Re: Counting letters in Strings.

    Here's a different approach:

    Row\Col
    A
    B
    C
    D
    E
    1
    Your Loads
    Random Loads
    Random Loads
    2
    ACB DED BDAD B2 and across and down: =RandStr("ABCDE", RANDBETWEEN(3,6))
    3
    DABA ADCD ABCBE
    4
    BAC CDC ABDAB
    5
    BACBDC DEBE
    6
    DABD EDCBD
    7
    8
    9
    10
    Bin Order
    Bin Order
    Bin Order
    11
    ACDABAC DEBACBDCD DABCEDABECBD A11 and across: =BestBins(A2:A6)


    The trucks always move forward, and the distance they have to travel is the length of the bins.

    If that's useful, I'll post the workbook.
    Last edited by shg; 06-03-2015 at 04:04 PM.

  21. #21
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Yes it would be very helpful!

  22. #22
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Sorry I saw what you meant. (LocationLetter-PreviousLocation) should be between absolut value brackets.

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

    Re: Counting letters in Strings.

    Here 'tis.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    This is really great!!! Tomorrow I will try to figure out the code. Thank you very much for taking the time! You are definitely a Guru!

  25. #25
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Sorry no errors display! Thank youu!

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

    Re: Counting letters in Strings.

    You're welcome.

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

    Re: Counting letters in Strings.

    Pondering this, you can also show at which bin each truck should be loaded, and the order in which the trucks should queue.

    Row\Col
    C
    D
    E
    F
    G
    H
    10
    Bin Order & Load Positions
    Queue Order
    11
    Items to Load
    12
    32
    ....^....1....^....2....^ ....^....1....^....2....^
    13
    Loads
    EBCEDBEAEDCBE
    Rank
    Loads
    EBCEDBEAEDCBE
    14
    BAE B A E
    3.0
    DCE DC E
    15
    DCE DC E
    2.1
    BAE B A E
    16
    ECDBEB E C DB E B
    7.2
    CDBAD C DB A D
    17
    CEBA CE B A
    5.3
    CEBA CE B A
    18
    CDBAD C DB A D
    4.4
    BDEAED B D EAED
    19
    BDEAED B D EAED
    6.5
    ECDBEB E C DB E B
    20
    EBCED EBC ED
    8.6
    EBCED EBC ED
    21


    The forum strips the spaces that makes the load positions align to the bin positions as they do on the worksheet.

  28. #28
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Shg,

    I dont understand what you are trying to do. Could you be a little more specific?

    Thanks

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

    Re: Counting letters in Strings.

    Suppose you have the loads in the col C:

    Please Login or Register  to view this content.
    Then the bin order is shown in D13, and D14:D17 shows which bin each truck should load each item from.

    The trucks should queue so the the first goes deep into the bins to get its first item, followed by the next and next, so as many can load items simultaneously as possible:

    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    That looks pretty interesting. What is the criteria you used for pondering column E.?

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

    Re: Counting letters in Strings.

    Col H is just col D in alphabetical order; that sorts strings with leading spaces to the top. Col E is a formula the calculates the order that col D values should appear in col H.

  32. #32
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Ok great.. could you send it to me? I believe it could be very hopeful

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

    Re: Counting letters in Strings.

    Ici ... .
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    05-08-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    27

    Re: Counting letters in Strings.

    Thanks shg!! Let me know if you think other cool ideas!

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

    Re: Counting letters in Strings.

    You're welcome.

+ 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. Filter for strings containing only capital letters?
    By AstToTheRegionalMGR in forum Excel General
    Replies: 5
    Last Post: 12-18-2014, 02:01 PM
  2. Function extract the uppercase letters from strings of text
    By Hellga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-01-2013, 05:48 AM
  3. Counting strings when may be 2 strings in a cell
    By kathyvanemm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-09-2013, 08:49 PM
  4. Finding the hidden words in strings of letters
    By Hitch75 in forum Excel General
    Replies: 12
    Last Post: 07-14-2011, 06:32 AM
  5. Identifying the Case of Letters within Strings
    By DougStroud in forum Tips and Tutorials
    Replies: 5
    Last Post: 10-28-2005, 11:35 PM

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