counting continuous letters in cells within a row

    hello everybody I am new to excel and I am trying to figure out how to count continuous letters in different cells in a row. the version of excel I have is 2003.
    the case will be like this:
    each row goes from b1 to cm1.each cell in that row contains 1 letter a, b or c.
    the letter b will have no influence in the result, it will not count.
    the letters that I would like to count are a and c. the results of a will be displayed in cell cn and the results of c in co.
    the result I am looking for is how many counts of 4 plus consecutive a and c are.
    ex:
    aabbbaaa cccbcc aaaacaca bbbbcbcccca in this case a= 2 four+ counts c=2 four+ cunts b don't matters.


    I have 50 rows in the document.
    thanks to all in advance. your help will be really appreciated.

    Re: counting continuous letters in cells within a row


    Hi,


    Can you attach a workbook with several examples and your desired result in each case?


    Regards

    Re: counting continuous letters in cells within a row


    In your sample I only see 1 run of 4 a's and 1 run of 4 c's.


    So unless I missunderstood, try:


    [COLOR="#0000FF"]=SUM(IF(FREQUENCY(IF(B1:CM1="a",COLUMN(B1:CM1)),IF(B1:CM1<>"a",COLUMN(B1:CM1)))>=4,1))[/COLOR]


    confirmed with CTRL+SHIFT+ENTER not just ENTER.


    Repeat formula with "c" for letter "c".

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: counting continuous letters in cells within a row


    hello xor lx. thank you for your fast response. here I will try to give you an idea of the problem. in this example I started from column b. from b-g find 1 count of 4+ a(5 total consecutive a because the b in cell d don't count) we have from cell n-s 1 count of 4 c (4 consecutive c because b in cell o and p don't count. from cell t-x find 1 count of 4 a (4 consecutive a because the b in cell v don't count) then cell ad=2 because 1 count of 5 consecutive a from cell b-g and 1 count of 4 consecutive a from cell t-x. in the cell ae=1 because 1 count of 4 consecutive c from cell n-s. with this table I am looking for a maximum of consecutive letters. trying to explain in more details I will tell you that aaaaaa equals 6 consecutive a. in cccbcaa equals 4 consecutive c because the b is not counting even if is located in the table. in abbaaa ccbcbcc we have 4 consecutive a (1 count) and 5 consecutive c (1 count of 4+). all the counts of 4 of more should be registered. thank you so much for your help I hope you can understand my explanation. don't hesitate in asking me anything that you need to know. thanks again.
    [TABLE="width: 1440"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]b[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"]b[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"]b[/TD]
    [TD="width: 64, bgcolor: transparent"]b[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]b[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]a[/TD]
    [TD="width: 64, bgcolor: transparent"]b[/TD]
    [TD="width: 64, bgcolor: transparent"]c[/TD]
    [TD="width: 64, bgcolor: transparent"][/TD]
    [TD="width: 64, bgcolor: transparent"][/TD]
    [TD="width: 64, bgcolor: transparent"][/TD]
    [TD="width: 64, bgcolor: transparent, align: right"]2[/TD]
    [TD="width: 64, bgcolor: transparent, align: right"]1[/TD]

    [/tr]


    [/TABLE]

    Re: counting continuous letters in cells within a row


    hello nbvc thank you so much for your response. I probably didn't explain my self correctly, sorry about that. in the example I posted you will find
    four different groups of letters for easy understanding.
    in the first group we have 5 consecutive a because the b are not counting and we are looking for groups of 4 or more letters.
    in the second group we have 5 consecutive c, remember the b don't count.
    in third group we have 4 consecutive a.
    in group four we have 5 consecutive c because remember the b don' t count.


    we are looking to register any group of 4 or more letters a or c without taking in consideration letter b in every row. remember each letter has a cell assigned.


    I will post another example to help :
    aacbccbbcacbacaaaccaaccbaabaabaaccbbbbcccbccbcc


    in this ex we have 47 cells, one for each letter.
    from cell 3-9 we have a group of 4 consecutive c (b don't count) this qualifies as 1 count of 4 (register)
    from cell 25-32 we have a group of 6 consecutive a (b don't count) this qualifies as 1 count of 6 (4 or more, register)
    from cell 33-47 we have a group of 9 consecutive c (b don't count) this qualifies as 1 count of 9 (4 or more, register)


    in the cells showing the results for that row we should see a=1 and c=2
    thank you so much in advance and I hope this help to understand.

    Re: counting continuous letters in cells within a row


    Assuming there are no blank cells in between the data, go to Name Manager and define two new names (important that you do this whilst the active cell in the sheet is somewhere in row 1):


    Name: Arry1
    Refers to: ROW(INDIRECT("1:"&COUNTIF($B1:$CM1,"<>b")))

    Name: Arry2
    Refers to: COLUMN($B1:$CM1)-MIN(COLUMN($B1:$CM1))

    Exit Name Manager.


    Enter this array formula** in CN1:


    =SUM(IF(FREQUENCY(IF(T(OFFSET($B1,,SMALL(IF($B1:$CM1<>"b",Arry2),Arry1),,))="a",SMALL(IF($B1:$CM1<>"b",Arry2+1),Arry1)),IF(T(OFFSET($B1,,SMALL(IF($B1:$CM1<>"b",Arry2),Arry1),,))<>"a",SMALL(IF($B1:$CM1<>"b",Arry2+1),Arry1)))>=4,1))


    And this small variation in CO1:

    =SUM(IF(FREQUENCY(IF(T(OFFSET($B1,,SMALL(IF($B1:$CM1<>"b",Arry2),Arry1),,))="c",SMALL(IF($B1:$CM1<>"b",Arry2+1),Arry1)),IF(T(OFFSET($B1,,SMALL(IF($B1:$CM1<>"b",Arry2),Arry1),,))<>"c",SMALL(IF($B1:$CM1<>"b",Arry2+1),Arry1)))>=4,1))


    Regards



    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Re: counting continuous letters in cells within a row


    How about just these 2 formulas?


    [COLOR="#0000FF"]=SUM(IF(FREQUENCY(IF((B1:CM1="a")+(B1:CM1="b"),COLUMN(B1:CM1)),IF(B1:CM1<>"a",IF(B1:M1<>"b",COLUMN(B1:CM1))))>=4,1))[/COLOR]


    [COLOR="#0000FF"]=SUM(IF(FREQUENCY(IF((B1:CM1="c")+(B1:CM1="b"),COLUMN(B1:CM1)),IF(B1:CM1<>"c",IF(B1:M1<>"b",COLUMN(B1:CM1))))>=4,1))[/COLOR]


    both confirmed with CTRL+SHIFT+ENTER not just ENTER

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: counting continuous letters in cells within a row



    Doesn't that give a positive count for a substring such as cabbac, or caabac, etc.?


    Regards

    Re: counting continuous letters in cells within a row


    Not sure what you mean.... should that not count as 1 for "a"? I could have misunderstood.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: counting continuous letters in cells within a row


    I thought the OP meant more that "b"s were to be ignored, i.e. you pass on to the next cell to consider, which is not the same as them being considered the same as e.g. an "a".


    Perhaps you're right, though.


    Regards

    Re: counting continuous letters in cells within a row


    I think that is what I assumed the first time around, but I guess we will soon find out, once the OP comes back to confirm ;)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: counting continuous letters in cells within a row


    Quote from NBVC;714545

    I think that is what I assumed the first time around, but I guess we will soon find out, once the OP comes back to confirm ;)


    Indeed!

    Re: counting continuous letters in cells within a row


    Quote from XOR LX;714543

    I thought the OP meant more that "b"s were to be ignored, i.e. you pass on to the next cell to consider, which is not the same as them being considered the same as e.g. an "a".


    Perhaps you're right, though.


    Regards


    hello guys sorry it took me this long to get back. I have been trying the formulas but remember I am new to excel. it looks complicated. and yes you are right the "b" should be ignore and count the next letter.
    aabccbacb not count
    aababbac 1 count of 4 "a"
    abbabaabbac 1 count of 4 or more "a"
    aaacbbcbccc 1 count of 4 or more "c"
    cbbcccabcbcbcbcc 1 count of 4 "c" + 1 count of 5 "c" = 2 counts for that row.


    remember the version of excel I have is 2003. another thing every row don't have the same amount of letters. because of this some row finish in one column and other row in different column, so always will be empty cells between the last cell in a row and the cell that display the result :



    aaacbbcbbbcccaaaaaaaabacccaccacacacabbaaa 2 1 in this ex 2 counts of "a" 1 count of "c"
    . . ... --------- - - --- "a" rep as - "c" rep as .


    ccccccaaaaacbcccaaabbbccabcacccbbaabba 1 2 in this ex 1 count of "a" 2 count of "c"
    ...... ----- . ... "a" rep as - "c" rep as .


    ccbcaaabbcabcaabcccabbbb 0 0 in this ex there is not letter showing 4 or +



    aababacccbcababaabacbbccbcbcaaabbaabacccbcccbcbcababaaaaaaaa 4 3 in this ex 4 counts of "a" 3 counts of "c"
    -- - - ... . - - -- -. .. . . --- -- - ... ... . . - - -------- "a" as - "c" as .



    please note that every row can finish in a different column and therefore the empty cells from the end of the row to the cells that display the result vary. also as mentioned every letter "b" be ignored and won't have a place in the count. please if you can help me out with the simple way of doing this I will really appreciate, remember my experience with excel is very limited. thank you guys.

    Re: counting continuous letters in cells within a row


    hello my friend it took me a while to get back. do you think there is an easy way to do this calculation? I posted a more clarified ex to let you know guys a more précised criteria.thanks in advance for your help.

    Re: counting continuous letters in cells within a row


    I think you need to post a workbook with some examples and your attempt at adapting the formulas given earlier.


    Regards

    Re: counting continuous letters in cells within a row


    hello xor lx sorry to ask you this but, how can I post the workbook with the examples? I should just copy the tables and paste here? I did that before but just the letters appear on the post, no columns or row. thank you for your help.

    Re: counting continuous letters in cells within a row


    Click on Go Advanced and scroll down to Manage Attachments.


    Regards

    Re: counting continuous letters in cells within a row


    hello xor lx finally I got the example. just in this case the letters have change but the idea is the same. now the letter that will not count is "t". so every time you see a "t" the count will continue with the next letter.("b" or "p"). if we find 4 or more consecutive "b" or "p" not taking in consideration the "t" . thank you so much for your help.

    Re: counting continuous letters in cells within a row


    Ppbbttbpppttbptbtpp not count final result 0 0
    bbbbpptttppbbtbtbtb 1 count of 4"b" 1 count of 4 "p" 1 count of 5 "b" final result 2 1
    bbbbbbbttbppppbpppptp 1 count of 8"b" 1 count of 4"p" 1 count of 5"p" final result 1 2
    bbbttpptpttbbtbttpptp not count since not letter is 4 or more final result 0 0

    Re: counting continuous letters in cells within a row


    I can't seem to find your attempt at adapting the solutions given in this file (?) In fact, I can't find any formulas in there at all (?)


    What attempts did you personally make at adapting/adjusting the solutions given to match your actual situation? Can you please re-attach with these attempts added?


    Regards

Participate now!

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

Register Yourself Login