Results 1 to 3 of 3

Thread: Assigning values to text and summing, need help with data organization

  1. #1

    Assigning values to text and summing, need help with data organization



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

    Hi,

    I am trying to find a good way to translate text (a word) to a numerical value and then summing if there are multiple words that apply.

    I am using excel to record pathology information from fish. For each organ I record any unusual findings, such as hemorrhage, ulcer, etc. For some organs there are more than 1 finding. Each finding can be mild, moderate or severe in severity. I assign a score of 10 for mild, 20 for moderate and 30 for severe. I want to retain the text in the spreadsheet so I can easily see what the finding was and then a have an adjacent column for the total score for that organ. So a basic organization of what I started doing looks like this:

    fish number skin skin score fins fin score total score
    1 normal 0 erode_mod 20 20
    2 ulcer_mod, hem_sev 50 hem_severe 30 80
    3 ulcer, hem 20 erode_mod, hem 30 50

    So for each fish there is a total score for each organ and a total score for the entire fish (sum of the scores for all organs). This is currently what was started by a colleague using VBA to create a lookup table that gets activated as an add-in. This is a very clunky approach and often the link to the table breaks resulting in many columns of error messages. We got it to work but I'm hoping there is an easier way. I would prefer to avoid the add-in route. The problem with this route is that the table has to include each variant of finding and severity such as ulcer, ulcer_mod, ulcer_sev.

    So can someone suggest a better way of doing this? I included multiple findings in the same cell because it can vary from none to 4 or 5, and with observations from 12 organs, the number of columns in the spreadsheet could be numerous. So I entered all findings from each organ into a single cell to keep things condensed. Maybe this is a bad idea as I can't find information on working with more than 1 piece of info in a single cell. If I split each finding out into a separate cell, the number of columns will grow to an unmanageable number. If someone has a better suggestion I'm all ears (eyes). I could keep it simple and just use the severity score and not include the name of the finding, but this still leaves me with potentially up to 4-5 findings per organ that need to be summed. With a grid of rows that identify the fish and columns that identify organ I am pretty much stuck with needing to enter multiple items in a single cell, unless someone can point out an alternative.

    Thanks for any help or suggestions and if I have not made this clear enough let me know.


  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    831
    Articles
    0
    Excel Version
    Excel 2010
    This should illustrate an alternate method.
    Just type in findings separated with a comma.
    You'll need to modify the macro to suit your sheet for all organs.
    Attached Files Attached Files

  3. #3

    Thanks

    Quote Originally Posted by NoS View Post
    This should illustrate an alternate method.
    Just type in findings separated with a comma.
    You'll need to modify the macro to suit your sheet for all organs.
    Thanks for the suggestion!

Posting Permissions

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