The phrase garbage in garbage out comes to mind here. SInce the raw data is inconsistently labeled any attempt to use the text in them to relate records will be problematic.
Somehow your going to have to find a way to identify which word in the text field is sufficient to serve as a key for your relationship. (How will filemaker "know" that "Apple" is significant and Small, red and yellow are not?)
I'm thinking of a keyword list based relationship here where each field computes a list of keywords which match to a text field where you the user select or enter a keyword to match.
Define a field, cKeyList as Substitute ( TextField ; " " ; ¶ ) where text field is the field where you find text like "red apples".
Now you can set up a relationship as Categories::Category = Products::cKeyList
A Category record with "Apple" in the Category field will then match to all products that contain "Apple" in this field.
Alternatively, you may want to assign category names or ID numbers to your records by performing a find for "apples" using Replace Field contents to assign a common value to identify the category, perform a find for "Peaches" and assign a different value... etc. until all records have an assigned category.
You might be able to do this with a script that loops through your category records, using the names as criteria in find requests performed on your product table.
I really appreciate the help. I think finding the key would not be so difficult, as I have a code that is appended each time there is a split, the code starts 1.1, and then when there is a split, it becomes 1.1.1 and 1.1.2, so I can parse the text to back out the relationship.
This is a great introduction to recursive data structures, and I think will be very helpful.