Counting rows based on multi condition match

Sciguy_1

I want to count rows in my dataset if 3 columns (A-B-C) match. I want to enter the value in a new column (D). Here is an example. Thank you for your assistance!

Column A	Column B	Column C	Column D (Counted Tally)
7	Z	S	4
7	Z	S	4
1	A	P	3
1	A	P	3
1	A	P	3
7	Z	S	4
5	E	M	2
5	E	M	2
7	Z	S	4

  

jules

Hi @Sciguy_1

There are a few ways you could go about this. I think a formula column approach is relatively straightforward, with two columns, first, one that counts the occurrences of a match incrementally, and then a second column that finds the max value of that count by group, which is what you're calling a counted tally here. Here's what that table looks like:

 

 

And here are the column formulas:

Counting in groups by the columns used for a match:

Col Cumulative Sum( 1, :Column A, :Column B, :Column C )

and the Max of that count column, also grouped by the columns used for a match

Col Maximum( :Count In Groups, :Column A, :Column B, :Column C )

Table is attached for your reference. 

 

Alternatively, if you wish to create a table that lists unique rows only once, with the number of times they matched, you can use Tables > Summary. Enter in your columns as Group, and hit okay. Here's what that setup looks like:

This will return a summary table that shows the unique combinations of values in those three columns, along with a column showing the number of occurrences in the data table.

Once you have this table, you can select, and then drag the N Rows column from the summary table directly into your original table, and JMP will handle all the Matching for you to update the original table with those values. Here's what that looks like:

 

 

Finally, the script for making the table is also saved as a Source script to the summary table:

Data Table( "Example.jmp" ) << Summary(
	Group( :Column A, :Column B, :Column C ),
	Freq( "None" ), Weight( "None" )
)

 

I hope this helps!

 

@jules 

Example.jmp