Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    near Boulder, Colorado, USA
    Posts
    112
    Thanks
    22
    Thanked 4 Times in 4 Posts

    Fill in a column based on contents of another column

    I have an Excel 2000 (sorry, can't afford to update MSOffice) 836-line spreadsheet (see attached) in which I want to accomplish the following:
    Examine each entry in J and compare it to all terms in A5:A13 and all in A21:A34. If a string found in A5:A13 appears in J, then enter the corresponding values of B5:B13 into column F and C5:C13 into G.
    For example, if "megalo" is found in J (e.g. J3 & J4), then F3 & F4 would receive the entry "Theropod" and G3 & G4 would receive "Megalosaur". If "sacr" is found (e.g. in J24), then "sacrum" is entered in H24.

    It would be nice if both arrays in A/B could be expandable, as new terms are used.

    I tried the Search function combined with IF, but was defeated by the fact that when Search fails to find the target string in a given text, it returns #VALUE!, which apparently cannot be processed by the IF and thus ends the search, rather than going on to look for the next string.

    836 lines is a bit beyond the number I would like to do by hand, thus this query.
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Goulburn. NSW, Australia
    Posts
    20
    Thanks
    3
    Thanked 4 Times in 4 Posts
    As you've outlined it, there's insufficient information to sort out the problem, because there's no data in C5:C13, it's unclear what you want to do with A21:A34 (in which there's also no data), and it's not obvious what the long forms of the values listed in column B are - how do you determine the long form of "zyg", for instance?

    One way to sort out how you specify the problem so that others can help, is to do a few entries by hand, and list the steps that you take, including where the data comes from, as you do so. You can then check the spreadsheet to ensure that all necessary data, including lookup tables, is listed, and also that all of the steps are there.

  3. The Following User Says Thank You to CephasOz For This Useful Post:

    cosmlou (2012-01-20)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    near Boulder, Colorado, USA
    Posts
    112
    Thanks
    22
    Thanked 4 Times in 4 Posts
    From CephasOz:
    As you've outlined it, there's insufficient information to sort out the problem, because there's no data in C5:C13, it's unclear what you want to do with A21:A34 (in which there's also no data), and it's not obvious what the long forms of the values listed in column B are - how do you determine the long form of "zyg", for instance?

    One way to sort out how you specify the problem so that others can help, is to do a few entries by hand, and list the steps that you take, including where the data comes from, as you do so. You can then check the spreadsheet to ensure that all necessary data, including lookup tables, is listed, and also that all of the steps are there.


    Sorry, apparently I uploaded an unfinished file. This one (Book3.xls) should be more coherent.
    Let me try again to describe the task:
    Entries F5:H30 are examples of the desired OUTPUT.
    They are the result of the following operations, done by hand, but which I wish to automate:

    Examine each entry in col. I and compare it to all terms in A5:A13. If a string from in A5:A13 appears in col. I, then enter the corresponding values from B5:B13 into column F and from C5:C13 (if any) into G.
    For example, if "megalo" is found in I (e.g. I3 & I4), then F3 & F4 would receive the entry "Theropod" and G3 & G4 would receive "Megalosaur".

    Also examine each entry in col. I and compare it to all terms in A21:A34. If a string from A21:A34 is found in col. I, then enter the corresponding value from B21:B34 into column H.

    The lists in A will be created by knowledgeable paleontologists, and may well grow larger (that is why I want to use such lists, rather than hard-coding the words into formulae--the address of a list can be changed more easily in a formula or VBA script). The specific meaning of "zyg" (zygapophysis) is immaterial for purposes of this example -- delete it from the A list if you like. In fact, all of the lists in A, B, and C can be reduced to 3 or 4 members if it simplifies the work of creating an example.

    Hope this is clearer.
    Attached Files Attached Files
    Last edited by cosmlou; 2012-01-20 at 16:32.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Cosmlou,

    Your attachment of Book3.xls seems to have failed. When I click on the link I get an "Invalid Attachment" message.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    cosmlou (2012-01-20)

  7. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    near Boulder, Colorado, USA
    Posts
    112
    Thanks
    22
    Thanked 4 Times in 4 Posts
    Curious. When I click on it, it opens fine. (And I did check to see that I was clicking on the downloaded version in the Temp folder, not the file that I uploaded (Documents folder).)
    Could I ask you to try downloading it again (after deleting the "bad" version, of course)?

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ok, Now it is working.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ok, here's what I came up with.

    Note: there is a Dynamic Named Range "FieldIDs" check out the defined names for it's definition. For this to work the way I set it up I deleted all rows that did not have a Field Idendification Entry.
    The macro is called OFS().
    On the Sheet Test I created a truth table to check my results against Sheet1. You will notice some False entries. The ones I checked had the correct values according to your rules as I understand them.

    Post back if you have any questions.

    BTW: If I were doing this from scratch I'd move both of the lookup tables to another sheet and setup Dynamic Range Names for each so it would be easy to add to them.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-01-20 at 19:28.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    cosmlou (2012-01-21)

  11. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    near Boulder, Colorado, USA
    Posts
    112
    Thanks
    22
    Thanked 4 Times in 4 Posts
    Many thanks! It appears you have done what I need, and I can fix the anomalies here and there. And I will see if I can implement your suggestions on moving the lookup tables, though I am not used to working with multiple interacting sheets, nor dynamic ranges. We'll see how it goes. If I don't succeed, I'll be back for more advice.

Posting Permissions

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