Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Re: Obtaining the row number of a cell that fulfills a certain criterion

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 3, 2013 11:10 PM
(7693 views)

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

Jesper

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 3, 2013 11:45 PM
(11164 views)
| Posted in reply to message from JesperJohansen 09/04/2013 02:10 AM

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.

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 3, 2013 11:45 PM
(11165 views)
| Posted in reply to message from JesperJohansen 09/04/2013 02:10 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jesper

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jesper

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Sep 4, 2013 2:45 AM
(6523 views)
| Posted in reply to message from JesperJohansen 09/04/2013 04:14 AM

This seems to work:

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

:name[r[Contains( :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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jesper

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Sep 4, 2013 4:36 AM
(6523 views)
| Posted in reply to message from JesperJohansen 09/04/2013 06:41 AM

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**]]**

**)**

**)**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jesper

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Sep 4, 2013 5:20 AM
(6523 views)
| Posted in reply to message from JesperJohansen 09/04/2013 08:02 AM

(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!