Don't see why you want to export to Excel for this.
I think that you originally used a return separated list of values in a text field as your way to list which which groups a given contact might be a member of. This can work quite well but is not as flexible as setting up a "many to many" relationship such as:
A looping script can pull the data from the original text field one value at a time and generate both the records in LIsts and in ListMember without need for exporting to Excel.
The many to many relationship is exactly how i want it now, so feeling chuffed that I'm on the right lines
What would such a script look like?
First, more relationship detail:
Contacts::__pkContactID = ListMember::_fkContactID
Lists::__pkListID = ListMember::_fkListID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Note that none of these "ID" fields store a contact name nor do they store list names. They should be either auto-entered serial numbers or text fields with auto-entered Get ( UUID ) values. For this script and many interface designs, "allow creation..." needs to be enabled for ListMember in the contacts to ListMember relationship.
To the above we add a new Tutorial: What are Table Occurrences? and relationship just for the purpose of moving this data. You can remove this TO when the job is done:
Contacts::TempTextField = Lists|Create::ListName
Lists|Create is the name of a second occurrence of Lists and "allow creation..." must also be enabled for it in this relationship.
Now we can script:
The script needs two loops, one "nested" inside the other. The outer loop loops through the records in contacts, the inner loops through the values in your text field. I am assuming two things here: 1) Your text field that lists your lists for a given contact is named contacts::ListMembers. 2) This field lists the names of lists and not ID numbers
#Starting from your Contacts layout...
Show All Records
Go to Record/Request/Page [First]
Set Variable [$K ; Value: 1 ]
Exit Loop If [ $K > ValueCount ( Contacts::ListMembers) ]
Set Field [ Contacts::TempTextField ; GetValue ( Contacts::ListMembers ; $K ) ]
#Create a new record in Lists if one with this name does not already exist.
Set Field [Lists|Create::ListName ; Contacts::TempTextField ]
#Create linking record in ListMember
Set Variable [ $ListID ; value: Lists|Create::__pkListID ]
Set Variable [$ContactID ; value: Contacts::__pkContactID ]
Go to layout ["listmember" (ListMember) ]
Set Field [ ListMember::_fkContactID ; $ContactID ]
Set Field [ListMember::_fkListId ; $ListID ]
Go to Layout [ original layout]
Set Variable [ $K ; value: $K + 1 ]
Go to Record/Request/Page [next ; Exit after Last ]
That is awesome I have to do the kids dinner now but I'll get that working this evening. BTW your assumptions were correct.