cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
JesperJohansen
Level IV

Obtaining the row number of a cell that fulfills a certain criterion

Is there a function that will return the row number of the (first) cell in a certain column that fulfills a criterion, e.g. begins with "XYZ" or is larger than X. That will allow me to obtain the data of a relevant cell in a different column, similar to the function of the VLOOKUP-function i Excel.

Preferably I want to do it in the Formula editor.

BR
Jesper
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Obtaining the row number of a cell that fulfills a certain criterion

I usually use Update and Join (or jsl) for VLOOKUP-like work in JMP.

But here's a way to do to it with a column formula:

:height[Minimum(

  Data Table( "Big Class.jmp" ) << get rows where( :name == "ROBERT" )

)]

The example is based on the Big Class.jmp sample data table. It returns the height of the first ROBERT in the name column. Just paste the code into the Formula Editor.

However, note that the table name must explicitly given in the formula for it to work. And if rows are added/deleted or cell values change this type of formulas that use jsl commands not found in the Formula Editor may not automatically update. If so, the "Apply" button in the formula editor needs another click.

View solution in original post

8 REPLIES 8
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Obtaining the row number of a cell that fulfills a certain criterion

I usually use Update and Join (or jsl) for VLOOKUP-like work in JMP.

But here's a way to do to it with a column formula:

:height[Minimum(

  Data Table( "Big Class.jmp" ) << get rows where( :name == "ROBERT" )

)]

The example is based on the Big Class.jmp sample data table. It returns the height of the first ROBERT in the name column. Just paste the code into the Formula Editor.

However, note that the table name must explicitly given in the formula for it to work. And if rows are added/deleted or cell values change this type of formulas that use jsl commands not found in the Formula Editor may not automatically update. If so, the "Apply" button in the formula editor needs another click.

JesperJohansen
Level IV

Re: Obtaining the row number of a cell that fulfills a certain criterion

Thank you. I have been looking for this for a long time!

BR
Jesper
JesperJohansen
Level IV

Re: Obtaining the row number of a cell that fulfills a certain criterion

Is there a way to add "By" criteria to this function. That is for example I want to find the row number of the first Cell in Column 1 that equals "X", but I want to do that separately for each value of Column 2?

I can do that with complicated If-functions, but it would be nice if I could do it similarly to the way the Col-prefixed statistical functions work.

BR
Jesper
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Obtaining the row number of a cell that fulfills a certain criterion

This seems to work:

r = Current Data Table() << get rows where( :height == 60 );

:name[r[Contains( :sex, :sex )]];

It finds the name of the first person of each sex with height = 60. Not sure if Current Data Table will work under every condition (e.g interferencve with independently run scripts)

JesperJohansen
Level IV

Re: Obtaining the row number of a cell that fulfills a certain criterion

Thank you. What if I have multiple "By" criteria? And where do I specify whether i searches for the first or last row fulfilling the criteria?

BR
Jesper
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Obtaining the row number of a cell that fulfills a certain criterion

The above works for multiple cases in the By column (i.e. not limited ro just "M" and "F" as in the example). But it will not work for several "By-columns" without complicating it too much. I suggest using a new column that combines the categories from multiple columns (e.g "age-sex").

Below is a sligtly modified version that 1) allows choosing the first or last row (change Min to Max to get the last row) and 2) prevents error if no rows would meet the criteria.

If( N Row( r = Data Table( "Big Class.jmp" ) << get rows where( :height == 60 ) ) > 0,

  If( (q = Min( Loc( :sex[r], :sex ) )) > 0,

  :name[r[q]]

  )

)

JesperJohansen
Level IV

Re: Obtaining the row number of a cell that fulfills a certain criterion

I think I solved it myself using your original suggestion:

a = :By Criteria 1; b = :By Criteria 2; :Column 1[Minimum(Current Data Table() << get rows where(:Column 2 == "X" & :By Criteria 1 == a & :By Criteria 2 == b))]

Do you see any reason why this should not work?

BR
Jesper
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Obtaining the row number of a cell that fulfills a certain criterion

(It looks like a and b are column variables, and I think that comparing column with a value would always return false.) <<wrong

Edit: On second thought it should work fine as long as the criteria is true for at least one row in each combination of by-categories (I was first thinking jsl, not column formula context where "current row" is assumed). Good solution!