Data extraction, manipulation, subsetting

[#1]

Hello everyone, I am trying to write a script that would help me look up the highest number in one column and extract the data from its adjacent column. However, the table I have looks somewhat like this:

 

I want something like this:

 

I hope somebody knows how to go ahead with such a problem. I have tried using transpose and Split options but they dont seem to work the best for me. Any help would be highly appreciated. 

[#2 · Accepted solution]

I've got you part way in the attached.  I created two new columns and then used tabulate.  You can then make that tabulation into a data table.  It has the information you want, but it is in columns rather than rows (and transpose won't work because of the differing data types).  But I'm not entirely sure what you are after, so perhaps you don't need it in the format you describe.

[#3 · Accepted solution]

Dale provided you with the functions to get the rows you need.

Amy, the table you desired is an Excel-like table. The numeric values will be have to be character  (in JMP) to create that table. JMP tables are database type tables where each column has a unique type.

The attached script will create the table you described, but it will not be of much use in JMP.  The most usable format for analyses is the stacked format of the subset table.  

This script also creates a display table from the subset table using Tabulate.

[#4]

Thank you for helping me.