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

- JMP User Community
- :
- Discussions
- :
- How do I make an if formula conditional on a row value appearing in ANY row in a...

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

Highlighted

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

Sep 7, 2018 7:25 AM
(8084 views)

Hi :)

I am looking for an interactive explanation to a sorting/filtering/list issue.

For example if my task would be: Export a list to excel of all customer purchases made by customers who have never bought coffee based on below dataset. The task is thus to export all rows from customer 2 and 4 to an excel and my challenge is how to make a selection / variable that is able to sort this out.

I have tried by creating a column with a binary "if" formula on whether or not coffee is in the row and then secondly a column displaying customer ID if the customer has bought coffee. Then I hoped to save this column as a list and make an if formula conditional on customer ID appearing in this list, such that for example all rows for customer 1 and 3 would have the same value and thus could be excluded. But I got stuck at the list part + it might not be the easiest way to do it.

Can anyone help?

Tried ”solution” | |||

Customer ID | Drink | Coffee or not | Customer ID who have bought coffee |

1 | Coffee | 1 | 1 |

1 | Coffee | 1 | 1 |

1 | Water | 0 | 0 |

1 | Coffee | 1 | 1 |

2 | Water | 0 | 0 |

2 | Water | 0 | 0 |

2 | Water | 0 | 0 |

2 | Water | 0 | 0 |

2 | Water | 0 | 0 |

2 | Water | 0 | 0 |

2 | Water | 0 | 0 |

3 | Water | 0 | 0 |

3 | Water | 0 | 0 |

3 | Coffee | 1 | 3 |

4 | Water | 0 | 0 |

4 | Water | 0 | 0 |

4 | Water | 0 | 0 |

4 | Water | 0 | 0 |

4 | Water | 0 | 0 |

4 | Water | 0 | 0 |

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

Hi @Nelle91,

Another method which works well for the case of selecting on the basis of just one item, is to utilize the Right Click > Select Matching. First, select a cell with coffee, right click > select matching, which selects all the coffee cells in that column. Then, click the customer ID column, and right-click an already selected cell > select matching. This will now select all those customers that had been selected because they had purchased coffee. You can now delete those rows and you're left with the subset of customers who never purchased coffee.

5 REPLIES 5

Highlighted
##

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

Re: How do I make an if formula conditional on a row value appearing in ANY row in another column?

Attached is your sample data table with the below formula applied to it

```
dt = Current Data Table();
currentCustomer = :Customer ID;
If(
N Rows(
dt << get rows where(
:Customer ID == currentCustomer & :Drink == "Coffee"
)
) > 0,
1,
0
);
```

Jim

Highlighted
##

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

Re: How do I make an if formula conditional on a row value appearing in ANY row in another column?

Hi @Nelle91,

If you would like to accomplish this in JMP without using any JSL, or even column formulae, you could use Tables > Summary to created a linked summary table that counts the number of drinks each customer has purchased. Then, using this linked Summary table, you could select the customers who purchased zero cups of coffee (which will also select those customers in the original table), and then subset those customers to a new table. Below is a quick video showing that process.

I hope this helps!

Highlighted

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

Hi @Nelle91,

Another method which works well for the case of selecting on the basis of just one item, is to utilize the Right Click > Select Matching. First, select a cell with coffee, right click > select matching, which selects all the coffee cells in that column. Then, click the customer ID column, and right-click an already selected cell > select matching. This will now select all those customers that had been selected because they had purchased coffee. You can now delete those rows and you're left with the subset of customers who never purchased coffee.

Highlighted
##

Another method that users seem to like is to create a new column in your data table, that has a formula for specific criteria. In this case, a column sum aggregated by customer ID will find out the count of Coffee Use. Then use Select Where or Select Matching Cell where the Coffee Use == 0. The column can be used for data filters etc.

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

Re: How do I make an if formula conditional on a row value appearing in ANY row in another column?

Highlighted
##

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

Re: How do I make an if formula conditional on a row value appearing in ANY row in another column?

Works so well and easy - thanks a lot!

Article Labels

There are no labels assigned to this post.