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

[#1]

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

[#2]

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
);

[#3]

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

[#4 · Accepted solution]

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. 

@julian

[#5]

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.

[#6]

Works so well and easy - thanks a lot!