Fill in a column based on contents of another column

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Cosmlou,

Your attachment of Book3.xls seems to have failed. When I click on the link I get an “Invalid Attachment” message. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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)?

Reply | Quote

Ok, Now it is working. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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. :cheers:

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.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote