If spelling error then sum

[ryanosullivan1983]

Hi everyone at mrexcel.com forum's, this is my first post so please be nice <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" alt="0" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\DOCUME~1\P86633\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata></v:shape><o></o>
<o></o>
I'm here to ask for your help, as I really can’t figure out how to do this... <o></o>
I been searching but have found no answers. So you the excel guru’s are my last resort.<o></o>
<o></o>
I'm looking at list of data for a website search engine.<o></o>
<o></o>
I have a list of search terms in column A and in column B there is the amount of times the search term has been searched for.<o></o>
<o> </o>
I need a formula that sums the amount of search performed against an invalid or misspelt search term.<o></o>
<o></o>
What I’m looking for is a formula that goes into column C which checks the cells in column A and if the cell contains a spelling error, number or a symbol (ie 123+-%!?) it returns a true or 1, if the cell contains a valid correctly spelt word it returns false or 0. Then I could use a simple SUMIF on the value returned to sum values in column B were C is 1. This would arrive at the amount of invalid search terms. <o></o>
<o> </o>
If there is a different way of doing this then I’m open to suggestion.<o></o>
<o></o>
Any ideas... please help?<o></o>

 

[Expiry]

How would you know if a word was spelt incorrectly?

 

[JSA715]

In VBA range.checkspelling can be used as boolean (if in the dictionary = true, if not = false). Maybe you could start there?

 

[zilpher]

I remember CheckSpelling from an old post, it doesn't work in a function without defining a new excel object IIRC. Using the Range object just shows the dialog (IIRC). I'm using my mac which only has Excel 2008 on it, no vba so this is a guess but:

public function SumIfMisSpelt(what as range, sum as range) as long
dim xlApp as new excel.application
dim lngTally as long
dim rngTemp as range

for each rngtemp in what
    if not xlapp.checkspelling(rngtemp.value) then lngtally = lngtally+ rngtemp.offset(0, sum.column-rngtemp.column).value
next
sumifmisspelt=lngtally
set xlapp=nothing
end function

That's had no checking at all (why oh why doesn't office 2008 have vba?) so it will need fixing. Pop that into a module, then go back to your sheet, type =sumifmisspelt(A1:A20,B1:B20) and press enter (assuming the misspelt words are in A1:A20 and the count is in B1:B20).

I'd be stunned if that works tho!

IIRC the checkspelling bug was listed in Daily Dose of Excel. Thats a good site!

HTH

 

[JSA715]

Can you explain what the IIRC is?

 

[zilpher]

Sorry, IIRC means "If I Remember Correctly"

 

[JSA715]

HAHAHA
Well that makes more sense...

 

[ryanosullivan1983]

I remember CheckSpelling from an old post, it doesn't work in a function without defining a new excel object IIRC. Using the Range object just shows the dialog (IIRC). I'm using my mac which only has Excel 2008 on it, no vba so this is a guess but:

public function SumIfMisSpelt(what as range, sum as range) as long
dim xlApp as new excel.application
dim lngTally as long
dim rngTemp as range

for each rngtemp in what
    if not xlapp.checkspelling(rngtemp.value) then lngtally = lngtally+ rngtemp.offset(0, sum.column-rngtemp.column).value
next
sumifmisspelt=lngtally
set xlapp=nothing
end function

That's had no checking at all (why oh why doesn't office 2008 have vba?) so it will need fixing. Pop that into a module, then go back to your sheet, type =sumifmisspelt(A1:A20,B1:B20) and press enter (assuming the misspelt words are in A1:A20 and the count is in B1:B20).

I'd be stunned if that works tho!

IIRC the checkspelling bug was listed in Daily Dose of Excel. Thats a good site!

HTH

Hey Zilpher,

Thanks for you reply, that kind of works... it gives a sum for mispelt words, however I need to also need it to sum for cells which start with number of symbol any could you suggest how?

 

[zilpher]

Hi

That worked straight off? Whoa, I'm amazed. However, here goes again. Once again no way of testing this so off the top of my head I'd change it to this:

public function SumIfMisSpelt(what as range, sum as range) as long
dim xlApp as new excel.application
dim lngTally as long
dim rngTemp as range

for each rngtemp in what
    if not xlapp.checkspelling(rngtemp.value) or gotasymbol(rngtemp.value) then lngtally = lngtally+ rngtemp.offset(0, sum.column-rngtemp.column).value
next
sumifmisspelt=lngtally
set xlapp=nothing
end function
	
	
private function GotASymbol(byref strWhat as string) as boolean
const strSymbols as string = "1234567890!""""%^&*$()"
for i = 1 to len(strsymbols)
        if instr(1, strwhat, mid(strsymbols, i, 1)) then
            gotasymbol = true
            exit function
        end if
next i
end function

I'm not sure the strSymbols string is comprehensive enough, change it to suit and I can never remember which way round the instr function works, is it string to search first or second? I suppose Google could be my friend here but hey, I'm off to a meeting

That's not very efficient or elegant but without Excel to play with I'm struggling to make it up as I go along...