count Re-occurrences

Originally Posted by stevekirk

hi bryan,

i thought i knew my way around your formulars but i guess not

please see attached file for example

the results i need are in red in ak2

steve

I used $Ai for the second, but it's not the correct cell.

Was there only AH to total? - if so rip out all after the + in the middle.

btw, the formula (as now) drags sideways AND downwards - still works


and don't put spaces after the words in row 1, otherwise "dis " won't match.

Cheers

Originally Posted by stevekirk

bryan,

i have attached a file with all the furmulars in that you have given me before

please see why it did not for work for me and adjust please

steve

A quick look (will be back in 2 days) suggests


AL1
dis = beam saw <<< you cannot search on this
AL2
=(LEN($AI2)-LEN(SUBSTITUTE($AI2,AL$1,"")))/LEN(AL$1)+((LEN($AJ2)-LEN(SUBSTITUTE($AJ2,AL$1,"")))/LEN(AL$1))
should be:
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,"dis","")))/3+((LEN(AI2)-LEN(SUBSTITUTE(AI2,"saw","")))/3)

AM1
edge <<<< if 'edge' is 'egbd'
AM2
=(LEN($AI2)-LEN(SUBSTITUTE($AI2,AM$1,"")))/LEN(AM$1)+((LEN($AJ2)-LEN(SUBSTITUTE($AJ2,AM$1,"")))/LEN(AM$1))
should be:
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,"egbd","")))/4

AN1
biesse
AN2
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,AN$1,"")))/LEN(AN$1)

AO1
alt
AO2
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,AO$1,"")))/LEN(AO$1)

AP1 ............ I presume you want to count 'bi'
bi
AP2
=(LEN(SUBSTITUTE(AI2,"biesse",""))-LEN(SUBSTITUTE(SUBSTITUTE(AI2,"biesse",""),"bi","")))/2

E2
_________________2L/2S
use
2L/2S
and align to right for your display.

AI2 is 30 always enough?
suggest 50
(I blew 30 in testing)

cheers
---

Originally Posted by stevekirk

hi, bryan

hope you had a good break

the 2l, 2l/2s in column e MUST ALWAYS be the last 5 charecters in the cell
this is because the field in the main frame has only 23 spaces

therefore ak has right(e2,5) this then unfortuatly leave spaces in front of the ones that say 2L OR 2S then your other formulars dont work unless i remove the spaces manaullly

can we put in another formular to overcome this

thanks

steve

One of two options, you can use Trim(A1) on any cell that has leading or trailing spaces to get the valid data, or, if you are trying to access 5 characters from A1 and it is only 1 character long use A1&" " etc
=Right(" "&A1,5)
or
=Left(A1&" ",5)
etc.

so right("_____"&E2,5)
should work for you. (with 5 spaces)

hth
---