Parse Data Into Unique Value List

[tbcomputerguy]

Hi i have a file with approx 800 records.  Each record will contain a field called participants.  No two records are the same but can contain the same participants...meaning other fields make them unique of course.  There are no duplicate dances.  But, record one could contain Ann smith, record two and others could contain various records like below.  I am given these records as presented.  they do the leg work I import it. I believe i have to convert it to a return separated list and then filter it to get the unique records.  Seems east for one field but for the whole group seems tricky.

evno    participants
1    Lydia Wiggins | Susan Barton
2    Susan Barton | Charlotte Watts | Jai Roberts
3    Charlotte Watts | Jai Roberts | Katelyn Parsons
4    Jai Roberts | Katelyn Parsons | Elena Sullivan | Leona Matthews | Bronwyn Ho
5    Katelyn Parsons | Elena Sullivan | Leona Matthews | Bronwyn Ho | Emmanuella Gunn
6    Ruth Stott | Rowena Hammond
7    Harri Hodson | Daisy-Mae Bolton
8    Wilma Heath
 

**Note: the Pipe Separator is how i get the data and how they would like it.

Resulting in a unique list as so:

Lydia Wiggins
Susan Barton
Charlotte Watts
Jai Roberts
Katelyn Parsons
Elena Sullivan
Leona Matthews
Bronwyn Ho
Emmanuella Gunn
Ruth Stott
Rowena Hammond
Harri Hodson
Daisy-Mae Bolton
Wilma Heath

Edited April 22, 2019 by tbcomputerguy

[comment]

Why is "Lydia Wiggins" broken into "Lydia" and "Wiggins"?

 

[tbcomputerguy]

Sorry Typo  :-).  I edited it and fixed it

Edited April 22, 2019 by tbcomputerguy

[comment]

The way your data is structured when you receive it is most inconvenient. If you have any control over this stage, you should use it to  change this, rather then wrestle with the results.

If that's not possible, start by defining a calculation field cParticipants (result is text) =

Substitute ( Participants ; " | " ; ¶ )

Next, define a summary field sParticipants as List of cParticipants. This will give you a return-separated list of all participants across the current found set. Now all that's left to do is reduce the list to unique values only. In version 16 or later,  this is easy to accomplish using the UniqueValues() function. In previous versions you could define a value list using values from the cParticipants field, and use it to filter the values from sParticipants. 

[tbcomputerguy]

I am pretty sure it is not possible to get the data any other way.  They use some special software designed to host dance competitions.  It sets up the schedule and spits it out like this in excel format.  Then I import it.  I suppose I could find and replace the Pipe with even a space.  I think they do it to make it easier to see all the participants in that number.  Some dances have 25 kids.  If they didn't put some sort of delimiter it might become hard to read.  If you suggestions by all means.  In the mean time i will work on the above.

 

Thanks,

Dave