| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ALTERNATIVE TO SUMPRODUCT NEEDED
Rog wrote...
I think you're missing the point here. .... No, Peo only got the string order wrong. Change his formula to =SUMPRODUCT(--(X2:X40000="warranty"), --ISNUMBER(SEARCH("switch",AD2:AD40000))) and it will produce the result you claim to be seeking. The ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating whether a substring exists in a longer string, though, FTHOI, this could also be done with (SUBSTITUTE(string,substring,"")=string) less efficiently (sometimes only one level of function calls is necessary). |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED
WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully! You were right... there was no misunderstanding! Thank you so much!! Roger "Harlan Grove" wrote: Rog wrote... I think you're missing the point here. .... No, Peo only got the string order wrong. Change his formula to =SUMPRODUCT(--(X2:X40000="warranty"), --ISNUMBER(SEARCH("switch",AD2:AD40000))) and it will produce the result you claim to be seeking. The ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating whether a substring exists in a longer string, though, FTHOI, this could also be done with (SUBSTITUTE(string,substring,"")=string) less efficiently (sometimes only one level of function calls is necessary). |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base? If
I add the word "switch" to one of the records it will not update to show the count plus one. Please advise. What is happening here? I have put the word at the beginning of the record and inthe middle of it. I am using "*" before and after the word. Is it because I have 40k records to update? Thanks "Rog" wrote: WOW! Thanks to you all!!! I have not completely tested it, but so far it seems to work beautifully! You were right... there was no misunderstanding! Thank you so much!! Roger "Harlan Grove" wrote: Rog wrote... I think you're missing the point here. .... No, Peo only got the string order wrong. Change his formula to =SUMPRODUCT(--(X2:X40000="warranty"), --ISNUMBER(SEARCH("switch",AD2:AD40000))) and it will produce the result you claim to be seeking. The ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating whether a substring exists in a longer string, though, FTHOI, this could also be done with (SUBSTITUTE(string,substring,"")=string) less efficiently (sometimes only one level of function calls is necessary). |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
Do you have your calculation set to automatic under
toolsoptionscalculation? What is the formula you are using? Be aware that any array formula will be slow calculating 40000 rows -- Regards, Peo Sjoblom "Rog" wrote in message ... WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base? If I add the word "switch" to one of the records it will not update to show the count plus one. Please advise. What is happening here? I have put the word at the beginning of the record and inthe middle of it. I am using "*" before and after the word. Is it because I have 40k records to update? Thanks "Rog" wrote: WOW! Thanks to you all!!! I have not completely tested it, but so far it seems to work beautifully! You were right... there was no misunderstanding! Thank you so much!! Roger "Harlan Grove" wrote: Rog wrote... I think you're missing the point here. .... No, Peo only got the string order wrong. Change his formula to =SUMPRODUCT(--(X2:X40000="warranty"), --ISNUMBER(SEARCH("switch",AD2:AD40000))) and it will produce the result you claim to be seeking. The ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating whether a substring exists in a longer string, though, FTHOI, this could also be done with (SUBSTITUTE(string,substring,"")=string) less efficiently (sometimes only one level of function calls is necessary). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
Here is the formula. I do have the auto calc set. I tried going manual and using F9, but that didn't change it either. =SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001))) Thanks. "Peo Sjoblom" wrote: Do you have your calculation set to automatic under toolsoptionscalculation? What is the formula you are using? Be aware that any array formula will be slow calculating 40000 rows -- Regards, Peo Sjoblom "Rog" wrote in message ... WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base? If I add the word "switch" to one of the records it will not update to show the count plus one. Please advise. What is happening here? I have put the word at the beginning of the record and inthe middle of it. I am using "*" before and after the word. Is it because I have 40k records to update? Thanks "Rog" wrote: WOW! Thanks to you all!!! I have not completely tested it, but so far it seems to work beautifully! You were right... there was no misunderstanding! Thank you so much!! Roger "Harlan Grove" wrote: Rog wrote... I think you're missing the point here. .... No, Peo only got the string order wrong. Change his formula to =SUMPRODUCT(--(X2:X40000="warranty"), --ISNUMBER(SEARCH("switch",AD2:AD40000))) and it will produce the result you claim to be seeking. The ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating whether a substring exists in a longer string, though, FTHOI, this could also be done with (SUBSTITUTE(string,substring,"")=string) less efficiently (sometimes only one level of function calls is necessary). |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
Correction: "warranty does not have the "*". BTW, "BELLOW*" is supposed to
pick up all forms of "BELLLOWS" (Sometimes they spell it wrong) but count it only once in any record. =SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001))) "Rog" wrote: Here is the formula. I do have the auto calc set. I tried going manual and using F9, but that didn't change it either. =SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001))) Thanks. "Peo Sjoblom" wrote: Do you have your calculation set to automatic under toolsoptionscalculation? What is the formula you are using? Be aware that any array formula will be slow calculating 40000 rows -- Regards, Peo Sjoblom "Rog" wrote in message ... WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base? If I add the word "switch" to one of the records it will not update to show the count plus one. Please advise. What is happening here? I have put the word at the beginning of the record and inthe middle of it. I am using "*" before and after the word. Is it because I have 40k records to update? Thanks "Rog" wrote: WOW! Thanks to you all!!! I have not completely tested it, but so far it seems to work beautifully! You were right... there was no misunderstanding! Thank you so much!! Roger "Harlan Grove" wrote: Rog wrote... I think you're missing the point here. .... No, Peo only got the string order wrong. Change his formula to =SUMPRODUCT(--(X2:X40000="warranty"), --ISNUMBER(SEARCH("switch",AD2:AD40000))) and it will produce the result you claim to be seeking. The ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating whether a substring exists in a longer string, though, FTHOI, this could also be done with (SUBSTITUTE(string,substring,"")=string) less efficiently (sometimes only one level of function calls is necessary). |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
|
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
"Rog" wrote...
Correction: "warranty does not have the "*". BTW, "BELLOW*" is supposed to pick up all forms of "BELLLOWS" (Sometimes they spell it wrong) but count it only once in any record. .... Is it BELLLOW, BELLOW or BELOW? Doesn't really matter. The * is superfluous. SEARCH("xyz*",Range) and SEARCH("xyz",Range) always return the same result. Wildcards in SEARCH are only useful between literal text, e.g., SEARCH("a*z",Range) which could match the alphabet, "Anzania", "a long time ago in Zimbabwe", but not "Zounds! Another 'a'!". Anyway, there's no hope for matching misspelled words unless you use approximate patterns that could match a lot of other text or unless you test all the allowed misspellings, e.g., test BELOW, BEELOW, BELLOW, BELLLOW, etc. Approximate text matching requires VBA/udfs. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
"Rog" wrote...
Here is the formula. I do have the auto calc set. I tried going manual and using F9, but that didn't change it either. =SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"), --ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001))) .... No, you can't use wildcards in simple equality tests - "warranty*" would only match substrings containing "warranty" immediately followed by an asterisk. If you want to match "warranty" at the beginning of the col X cells, only check the first 8 chars of each of those cells. And the SEARCH string "BELLOW*" could be replaced with "BELLOW" because there the * is superfluous. =SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ), --ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001))) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
ALTERNATIVE TO SUMPRODUCT NEEDED - THERE'S A PROBLEM!!
Okay, but I still don't understand why if I add the word "BELLOW" to one of
the records and it goes through its 3 minutes of updating, it does not change the number of "BELLOW" it sees. Any thoughts there? "Harlan Grove" wrote: "Rog" wrote... Here is the formula. I do have the auto calc set. I tried going manual and using F9, but that didn't change it either. =SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"), --ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001))) .... No, you can't use wildcards in simple equality tests - "warranty*" would only match substrings containing "warranty" immediately followed by an asterisk. If you want to match "warranty" at the beginning of the col X cells, only check the first 8 chars of each of those cells. And the SEARCH string "BELLOW*" could be replaced with "BELLOW" because there the * is superfluous. =SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ), --ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF and SUMPRODUCT help needed | Excel Discussion (Misc queries) | |||
| Alternative to SUMPRODUCT? | Excel Discussion (Misc queries) | |||
| Which is faster? SUMPRODUCT or VLOOKUP, or another alternative? | Excel Worksheet Functions | |||
| How to select data series to format? (alternative needed) | Charts and Charting in Excel | |||
| Vlookup Alternative Needed | Excel Discussion (Misc queries) | |||
Hybrid Mode
