Count triples

[SANDRADEPAR]

Can someone give some hints???
Id like to know if someone have a code for visual basic/ excel to calculate the ocurrence of triples or triplets.
Because id like to count the ocurrence of triples numbers in a DNA or RNA chromossome , in order to verify certain parameters in sequences like
16 11 88 32 25 0 70 78 73 61 90 89 46 95 6 33 34 21 14 22
76 63 18 44 84 11 55 93 8 29 82 62 17 64 24 14 37 97 49 51
34 8 10 11 99 28 67 20 55 74 45 48 85 91 79 57 47 22 17 54
14 67 12 55 25 60 51 30 41 35 78 83 93 64 98 39 44 49 5 63

the ocurrence of triple (8,11,77) for example.

I'm looking forward to receiving good news.

Thanks in advance

[debasisdas]

What is the logic to implement that ?

[Guido Geurs]

dear,

I hope this will help You (see attachment)

Select a TXT file and open the file by clicking on "Open".
Click on "Search" to view the numbers in the listbox.

Ps :
- in your example, must it not be the triples 11, 14 and 55 ?
- If you want the Singles, Dubbles, ... it's just a small modification !!


br,

[Guido Geurs]

dear,

Is the result for your example like this: for 8 - 11 - 74

16 11 88 32 25 0 70 78 73 61 90 89 46 95 6 33 34 21 14 22
74 63 18 44 84 11 55 93 8 29 82 62 17 64 24 14 37 97 49 51
34 8 10 11 99 28 67 20 55 74 45 48 85 91 79 57 47 22 17 54
14 67 12 55 25 60 51 30 41 35 78 83 93 64 98 39 44 49 5 63

Result= 2

Is this what you want?

br,

[Guido Geurs]

dear,

Is this a solution? (see attachment)


br,

[Guido Geurs]

dear,


I have prepared something in Excel. (see attachment).
Is this what you are looking for?

If you want to test it, just run the macro "Find_Triples" or click in the toolbar on the button "123".

Remark: I have limited the run to the 10 first triples because there are 161.700 combinations of 100 numbers in groups of 3.

You can change this by changing the range in the line =

'§ VVVVVVVVVVVVVVVVVVVVV can be deleted
   Call Scan_Triples_Column("A1:A10")
'§AAAAAAAAAAAAAAAAAAAAAA !!!!! Next line de-comment if deleted !!!!!

if you want the first 100 triples, change the range to ("A1:A100")

I have done this because on my PC( Pentium 4 : 2.6 GHz ) it takes 7 min. for the first 1.000 triples.
So 161.700 triples = .... HOURS !!!!

Is this the right way am working ?
If so, I will try reduce the duration by searching an other algorithm for the scan.
Maybe with the Excel fomula "Match" ???


br,

[Guido Geurs]

Dear,


I hope this will help. (see attachment).
The results are in an array.
You can dump them in any lay-out in a sheet; (I have just written a code for columns)

br,

[Guido Geurs]

dear,

I have downloaded and opened the attachment without any problems.
Anyway here is it again.
Sorry but attaching the same file again (even with a different name) don't works in Bytes.
I have added a empty TXT file in the ZIP and now it's OK.

Good luck.


br

[Guido Geurs]

dear,


Sorry, my mistake: I have split the code for error tracking and did not redirect the macro in the button.

I have attached the updated version.

PS:
- if it don't work, run the macro "Sheet1.Do_All" manually.
- I have also entered lines for fast controle=

'§ VVVVVVVVVVVVVVVVVVVVVV 120 TRIPLES :just for testing = can be deleted
'   For FIRST_idx = 1 To 8
'      For SECOND_idx = FIRST_idx + 1 To 9
'         For THIRD_idx = SECOND_idx + 1 To 10
'            TOTAL_TRIP = "120"
'§ AAAAAAAAAAAAAAAAAAAAAA if deleted or commented, de-comment FINAL CODE
'§ VVVVVVVVVVVVVVVVVVVVVV 19.600 TRIPLES :just for testing = can be deleted
'   For FIRST_idx = 1 To 48
'      For SECOND_idx = FIRST_idx + 1 To 49
'         For THIRD_idx = SECOND_idx + 1 To 50
'            TOTAL_TRIP = "19.600"
'§ AAAAAAAAAAAAAAAAAAAAAA if deleted or commented, de-comment FINAL CODE
'§ VVVVVVVVVVVVVVVVVVVVVV FINAL CODE
   For FIRST_idx = 1 To 98
      For SECOND_idx = FIRST_idx + 1 To 99
         For THIRD_idx = SECOND_idx + 1 To 100
            TOTAL_TRIP = "161.700"
'§ AAAAAAAAAAAAAAAAAAAAAA

If you want to use the triples from 1 to 10: decomment the first code and comment the last code.

If you want to use the triples from 1 to 50: comment the first code, de-comment the second code and comment the last code.

If you want to use the triples from 1 to 100: comment the first code and the second code and de-comment the last code.



br,

[Guido Geurs]

dear,

You are right, I did the mistake to test it with triples from 1 to 50.
There are 19.600 combinations in groups of 3 and "integer" goes to 32.767.
there are 161.700 combinations for 1 to 100 !
So the vars must be from the type dimension "Long".

This is the right code:

Function Dump(FROM_idx As Long, TO_idx As Long)
Dim ARR_TRIP_idx As Long

I'm very sorry for this wrong calculation of me.
Attached is the modify-ed sheet with the results for triples from 1 to 100.

br,

[Guido Geurs]

Dear,

Attached is a new Excel sheet which is much faster in dumping the results.

br,