Most likely this will require few steps due to the character column. This process should work, I didn't have your data so there might be same mistakes / misunderstandings.
Starting table

Create summary table (ignore the character for now)

Then delete rows which have no character (column 5) or create subset with only those which have values

And use update and join with the first two columns


JMP is most likely able to write a script for this whole process or create a workflow
-Jarmo