Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEAThis exercise is very familiar; I know I've done this it before, perhaps as part of a training.
Here is my solution
I seem to be doing these challenges in more steps than some of the other solutions ... still need to work on my optimising!
I used 2 RegEx tools to slice out the PMID's and then the FAU's.
(PMID).*(\d{8}).*
(FAU - )(.*)
I then used Multi-Row formula tools to "fill" the rows - you need to do this step otherwise you can't associate the PMID's to the FAU's.
Using the Unique tool I then extracted out which are the unique combos of PMID/FAU.
A little Filter tool to clean up the data.
I then build out the column header for the Cross Tab so it can pivot the data
was thinking about it too hard. came up with a simpler solution