# Counting letters in Strings.

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

4. ## Re: Counting letters in Strings.

stnkynts,

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. ## 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. ## Re: Counting letters in Strings.

Thank you stnkynts.

Lets see if someone will come with an answer.

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

8. ## 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. ## Re: Counting letters in Strings.

Can you share the purpose of what you're trying to do?

10. ## 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 truck´s 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. ## 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?

12. ## Re: Counting letters in Strings.

Ok.

So S´s 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. ## Re: Counting letters in Strings.

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

14. ## 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. ## 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 S´s 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 B´s (Bins orders). (I already coded this part and it prints in my excel sheet). So I have all my S´s (which were given my me ¨the user¨) and the program calculated all my possible B´s.

Now, with that information I want to evaluate each S´s in all the B´s 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 B´s for the loads S´s plugged by the user. Here is where I am stuck with the coding!

I hope you can help me solve this.

16. ## 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. ## 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 don´t 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. ## 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.
Originally Posted by salasmau
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. ## Re: Counting letters in Strings.

Yes, that algorithm worked for when I don´t 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. ## 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.

21. ## Re: Counting letters in Strings.

Yes it would be very helpful!

22. ## Re: Counting letters in Strings.

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

Here 'tis.

24. ## 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. ## Re: Counting letters in Strings.

Sorry no errors display! Thank youu!

26. ## Re: Counting letters in Strings.

You're welcome.

27. ## 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. ## Re: Counting letters in Strings.

Shg,

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

Thanks

29. ## 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. ## Re: Counting letters in Strings.

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

31. ## 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. ## Re: Counting letters in Strings.

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

Ici ... .

34. ## Re: Counting letters in Strings.

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

35. ## Re: Counting letters in Strings.

You're welcome.

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