Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Inbed search function inside array

    Loungers, I am using the array formula below to sum the number of occuraces of a value @ $E8 (ie Level One - Environment - Small localised spill) in a particular month in the worksheet, this work great.

    =SUM((Worksheet!$H$14:$H$999=$E8)*(DATE(YEAR(Works heet!$B$14:$B$999),MONTH(Worksheet!$B$14:$B$999),1 )=K$7))

    The probem is that the data in colum H of the worksheet may contain other text and in different places in the same cell , ie

    Level Two - Minor Plant Damage, Report Only, Level One - Environment - Small localised spill, Level One - Financial loss < $1000
    Level One - Environment - Small localised spill, Level Two - Serious Potential Incident, Level Three - Major Plant Damage

    Is there away to encorporate a function that will look in the cells and sum the number of occurances (Level One - Environment - Small localised spill) regardless of where the sting is and what other text that there maybe in the cell.

    I hope that makes sence.

    Any thoughts/suggestions would be much appreciated

    Cheers

  2. #2
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Anyone have any thoughts?

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bend, OR
    Posts
    67
    Thanks
    10
    Thanked 2 Times in 1 Post

    Good news = a solution; Bad news = you won't like it

    I have faced this sort of problem and never found an easy answer. It should be possible using some sort of wild card to stand for the characters you don't need included. BUT...I can't find the wild card function AND you'll find that minor variations mess you up every time. (i.e.: Level One; Level one; Level 1; and level one are not the same.) No one is so well-disciplined that this will not happen.

    Here is the best solution I have found:
    • Set up your columns to contain just one type of data: Level, Source, Condition, whatever.
    • Parse the single data column into these specific columns. Your example makes the '-' and ',' look like the break points for each data type.
    • I personally would reduce the data. e.g.: 'Level One' = 1 in the column for Level. So does 'level 1' = 1. Trust me: when you have parsed and sorted the data you will see the variations and they will be numerous.
    • You can establish one column that recombines the text formatted for publication in a strict syntax if you wish.
    • You can count, sort, and extract the records you want based on the columns.
    • As a corollary benefit, your data collection procedure will need to change.
    This is a huge hassle but you are better off in the end and your data is more reliable and much more usable. Your spreadsheet is fundamentally flawed because its single-cell/multiple data approach. It is designed for data entry convenience but was not designed to both record and process the data it contains.

    ~~~~~~~~~~~~~~~~

    There is a SEARCH function you can explore. It sets up a sub-table based on the string. Unless your data entry is VERY disciplined this will exclude instances containing alternate forms.

    Another solution, depending on your data set size, is to take a pencil and piece of paper and simply count them. Crude, but it does allow visual editing of data variations like 'One', 'one', and '1' (to say nothing of 'I'.)
    Last edited by Magna; 2012-10-04 at 00:59. Reason: There is never just one answer.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Magna

    Thanks for the reply. The data is exported from an online reporting database, so the terminologies and the text always stay the same but can be in a different order each time.
    And there can be hundreds of records. So I need to manually go though each record and delete other strings of text, thats why I was hoping that there was away to search for the string using a formula and sum the product. However the different strings are always seperated by a ','

    I hope that there is a solution out there someplace.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    There are several solutions.

    My choice would be to start with this test:

    Code:
    =--(ISNUMBER(SEARCH("your text"A1)))
    This will return TRUE if your text is included anywhere in cell A1, and FALSE if it is not.

    I am guessing from the formula you submitted that you are able to "take it from there". Post back if you can't.

  6. The Following User Says Thank You to MartinM For This Useful Post:

    Magna (2012-10-04)

  7. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Martin - Will give your choise a shot tomorrow and see how it goes. Any problems will post back.

    Thanks for your help

  8. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bend, OR
    Posts
    67
    Thanks
    10
    Thanked 2 Times in 1 Post

    SEARCH should work

    MartinM's suggestion should work if the strings are machine-generated. I've never used SEARCH functions but could use them to simplify some operations. Thanks, Martin.

  9. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Magna - I have embedded your suggestion (where I assume it belongs) into the array formula, but its not returning any results

    =SUM((Worksheet!$H$14:$H$999=--(ISNUMBER(SEARCH("*enviroment*",$E8)))*(DATE(YEAR( Worksheet!$B$14:$B$999),MONTH(Worksheet!$B$14:$B$9 99),1)=K$7)))

    Any thoughts?

  10. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I don't think that you can use wildcard characters in the way you have. Just pick some unique part of the text in the condition you are testing for.

  11. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    MartinM I tried it without the wildcard but got no results, so tried the wildcards to see if that would work - but no luck either way.

    Any other thoughts?

  12. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Please excuse the hurried response - I am just going out to dinner !

    You've got in a muddle with your logic.

    Leaving aside your other tests for the moment, the number of occurrences of the string environment in the range $H$14:$H$999 is given by the array formula

    {SUM(--(ISNUMBER(SEARCH("*environment*",$H$14:$H$999))}

    However three thoughts in passing:

    1. When you are using the value in E8, either you need to express it as $E$8 on your formulas or, better, give it a meaningful NAME.

    2. You have mis-spelled environment, maybe just a transcription error in your post ?

    2. A SUMPRODUCT expression would IMH0 be much simpler to use in this case.

  13. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    MartinM - thanks for your help. Will try it out and see how it goes on Monday.

    Could you expand on the SUMPRODUCT option - also what does IMHO mean?

  14. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Had a lovely dinner with friends - am now back !

    Sorry about the IMHO - I don't usually use these abbreviations but, as I said, I was in a rush: In My Humble Opinion.

    There are many full descriptions of SUMPRODUCT on the net but a common way it is used is as follows.

    Let's say you want (as you do) to add together items which meet certain criteria. So you would construct an expression as follows:

    SUMPRODUCT(<a range of cells containing the data you want to add up>*(criterion1)*(criterion2)*(criterion3) etc)

    So it might look like SUMPRODUCT((H1:H200)*(H1:H200>100)*(K1:K200<$A$8)* (L1:L200<Now()-2))

    The first expression (H1:H200) is the values themselves and the subsequent expressions all return 1 if TRUE and 0 if FALSE:
    (H1:H200>100) true if the value itself is greater than 100
    (K1:K200<$A$8) true if a corresponding value (same row as) in another column is less than some constant or other
    (L1:L200<Now()-2) true if a date associated with (same row as) the value is more than 2 days ago


    So each item in H1:H200 is only added into the sum if all the criteria are TRUE. If any criterion is FALSE then its value will be 0, the product will be zero, and it will not be added into the sum.

    There are a number of provisos, two in particular:

    1. Every range must occupy the same rows or columns (hence in the example, all the ranges are nn1:nn200).

    2. You can only have 30 criteria - enough for most cases.

    Note that in constructing and debugging this sort of long expression the task is always much easier if you give the ranges meaningful names eg

    SUMPRODUCT((Invoices)*(Invoices>100)*(ItemsOnInvoi ce<SingleShipmentLimit)*(DeliveryDate<Now()-2))

    Hope this helps.

  15. #14
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi MartinM - Glad you enjoyed your meal, I'm sure that it would have been very nice , It will take me awhile to digest your suggestions, will try out the option next week and see how it goes. Thank you for your efforts, will let you know how it goes.

    Thanks again

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •