BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
Nelle91
New Contributor

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

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 IDDrinkCoffee or notCustomer ID who have bought coffee
1Coffee11
1Coffee11
1Water00
1Coffee11
2Water00
2Water00
2Water00
2Water00
2Water00
2Water00
2Water00
3Water00
3Water00
3Coffee13
4Water00
4Water00
4Water00
4Water00
4Water00
4Water00

 

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
julian
Staff

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

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. 

Select Matching.gif

 

 

 

 

@julian

5 REPLIES 5
txnelson
Super User

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
julian
Staff

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!

@julian

 

(view in My Videos)

julian
Staff

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

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. 

Select Matching.gif

 

 

 

 

@julian

gzmorgan0
Super User

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

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.

image.png

Nelle91
New Contributor

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!

0 Kudos