Help!Routing Combination

[wydn]

Hey guys..
I am a newbie in this forum.
I want to ask about combination procedure for following problem:
I have 9 routes:
1. AB
2. BC
3. AD
4. AC
5. CD
6. DE
7. AE
8. AB
9. AC
I want to display all possible combination of these routes with contraint the routes must include node ABCDE. I thought it will be more easier to do that using vba macro excel.
Thanks for the help.

 

[VoG]

Myrna Larson's code may help you http://www.mrexcel.com/forum/showthread.php?t=379323

 

[wydn]

thx u for the help...the code was work for me.
but i need another function so that i can count the number of character in one cell.
i.e cell A1 contain letters ABDAB
how to count how many "A" in that cells so can I get "2" in cell A2.

 

[VoG]

Try

=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))

 

[wydn]

Thank u my man!!!...
sorry i'm a beginner in VBA.
I need to do that action with 200.000 data.

So how to use that function in VBA so i can use that function for many data.
i.e I put my data in range A1:A200.000.

thanks for the help.

 

[VoG]

Try this: results in column B

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:B" & LR)
    .Formula = "=LEN(A1)-LEN(SUBSTITUTE(A1,""A"",""""))"
    .Value = .Value
End With
End Sub

 

[wydn]

thank u bro..That code is working!!!
But I have another problem when i try to do 4 combination.The results are too many so. Whereas I need to do until 8 combination.

I have the procedure to solve that problem, but I can't make the code.I hope u can help or give me another suggestion to solve the problem.

Here my procedure. I'll explain it by example so it easier to understand.
i.e I have letter in range A1:A6.
I need to know all possible combination which is containt letters ABCDE.

DC
AB
AD
CD
CE
E

1. check the first rows
2. check "Is the number of character 5? if yes check "Is the letter
A,B,C,D,E > 1. If yes delete combination. go to second row (repeat step 1 and 2). if no SaveCombination and write in another worksheet.
3.Continue step 2. if the number of character < 5 combine first row to second row. (loop to step 2)

Sorry if my explanation is complicated. That because english is not my basic language.

Thank u for the help!!!