count number of varying sequences

  • Have work with 2 to 5 steps using 8 possible processes. I need to count the number of sequences. Below, I found 5 sequences of 3,4,6,8 and 8 sequences of 4,6,8. I counted 5 and 8 manually. This is a small subset where counting the full list is difficult.


    Second, I want to count the number of pairs. Below there are 24 6,8's and 13 4,6's.


    The data below are in rows with two to five cells in each row.
    1 3 4 6 8
    1 3 4 6 8
    1 3 4 6 8
    1 3 4 6 8
    1 3 4 6 8
    1 3 4 7 8
    1 3 6 2
    1 3 6 8
    1 3 6 8
    1 3 7 6 8
    1 4 6 8
    1 4 6 8
    1 6 8
    2 4 6 8
    2 4 6 8
    2 4 6 8
    3 4 6 8
    3 4 6 8
    3 4 6 8
    3 4 7 6 8
    3 6 8
    3 6 8
    3 6 8
    6 8
    7 4 5 6 8
    7 6 8

  • Re: count number of varying sequences


    A possibility can be to put the data for each row in only one cell, then to remove the space between figure and use a filter: Custom filter > Contains >
    3468
    468
    68
    46

    Triumph without peril brings no glory: Just try

  • Re: count number of varying sequences


    Thanks. Your idea definitely works. I will use it.
    However, your method requires filtering by hand all possibilities the using CountA function. Again, that definitely works. Is there a more elegant idea that does not require manually filtering each combination?

  • Re: count number of varying sequences


    A possibility could be according to the data sent
    Note: for sequence 468 I don't understand your manual result
    Assuming
    Q2 = 3468
    then
    Q1 =SUM(NOT(LEN(SUBSTITUTE(SUBSTITUTE($A$3:$A$28 & $B$3:$B$28 & $C$3:$C$28 & $D$3:$D$28 & $E$3:$E$28," ",""),$Q$2,""))=LEN(SUBSTITUTE($A$3:$A$28 & $B$3:$B$28 & $C$3:$C$28 & $D$3:$D$28 & $E$3:$E$28," ","")))*1)
    Confirm using CONTROL + SHIFT + ENTER for array formula
    See file attached for more details

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!