Results 1 to 5 of 5

Thread: Help with Functions to Analyze Text within database

  1. #1

    Help with Functions to Analyze Text within database



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I've been given a task which is largely manual but I'm sure there are functions which could automate the process. Basically I have a spreadsheet which has a series of text values in column A which start "3000 ABC 2015", "3000 DEF 2013" etc. We're currently manually highlighting column E of the spreadsheet for all the occurrences of "3000 XXX XXXX" and then noting the total SUM value this gives (from the bottom toolbar), along with the number of occurrences (again from the bottom toolbar). Finally we copy and paste the values from column E to another spreadsheet and sort to get zero values. The number of occurrences, sum total of these occurrences and zero values are then manually recorded on another spreadsheet. As I say I'm sure this could be automated but haven't been able to work out how to do this. I try "3000" as my search value but nothing is returned. I just want to look up the first four digits of the values in col A and their corresponding values in col E. Also, there are occurrences when the total items and summed value looks at more than one value, say 3001 - 3003, how I can I add extra search values to the formula?

    Sorry, very much a novice at this so any help greatly appreciated!

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    plz see the file. Hope this help ?
    Attached Files Attached Files

  3. #3
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    please see the file. (without helper coloum).
    Attached Files Attached Files

  4. #4
    Quote Originally Posted by sambit View Post
    Hi,
    please see the file. (without helper coloum).
    Hi Sambit - thanks for the help but its not quite what I need. I've created a sample attached and I'm interested in col A and col B. In this instance 3000, 3005 and 3010. So I want to read across to column B for the number of times each of these occurs and the sum value of these in col B. Lastly I want to extract the number of zero values in column B for 3000, 3005 and 3010. I've simplified this but the actual spreadsheet has circa 200 lines for each value. In some cases the numbers are much smaller, so the parameter might be to search for values of 3020 - 3045 for example. If you can find a quicker way than simply eyeballing the corresponding numbers and highlighting col B it would save me a load of time! Really appreciate your help.
    Attached Files Attached Files

  5. #5
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    Plz find attached file. You get two type of solution. One is solved by formula and another one is Pivot Table. If you think the above solution is not full fill your requirement. Then please describe exactly what u want?
    Attached Files Attached Files

Posting Permissions

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