Re: counting continuous letters in cells within a row
hello here I tried to insert the formulas you gave me before. I already change the letters in the formula to match the new letters. I really don't know where to insert the names as array1 and array2. all I can get as you will see is name inside cell cn1 and co1. how it will work for the whole document when the formulas are just in row 1? I really appreciate your help thank you.
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
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
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
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
Quote from XOR LX;714543I 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 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
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"]
[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]
[/TABLE]
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.
