How do I find rows with the same values for any two columns in a given column?

[#1]

For example, columns 1 —— 3 here:

dt = Open( "$SAMPLE_DATA\Missing Data Pattern.jmp" );
dt<<New Column("T1",formula(If(	As Column( 1 ) == As Column( 2 ) | As Column( 2 ) ==	As Column( 3 ) | As Column( 1 ) == As Column( 3 ),	1)));dt<<run formulas;Column("T1")<<deleteFormula;

Thanks!

[#2]

• I tried to simplify it:

dt = Current Data Table();
x = dt << Get As Matrix();
For( i = 1, i <= N Row( dt ), i++,
	For( j = 1, j <= 2, j++,
		If( x[i, j] == x[i, j + 1],
			x[i, 5] = 1;
			Break();
		)
	)
);

dt[0, 0] = x;

[#3]

@lwx228, @txnelson ,

Just for the fun of it...

• Added @lwx228 example data to @txnelson , see attached JMP data table JN_AdjacentPlus.jmp
• The script below is embedded in the table. The script is named "Find any rows with matching adjacent columns", actually matching values not empties.
• Note #1: This script will take any number of columns. The columns do not need to be adjacent in the data table, just change the column specification in the definition of xmat.
• Note #2: Abs(x1-x2) ensures adjacent matches return zero and all other values are > 0.
• Note #3: Vmin() finds the minimum of each column of a matrix. So, transpose( Vmin(transpose(Abs(x1-x2))) ) returns the row minimum of Abs(x1-x2)
• Note #4: Loc( vec ==0 ) returns the row numbers whose value is zero.
• Note #5: Jim's solution finds any match, this script finds adjacent matches.     

Names Default to Here(1);

dt = current data table();

xmat = dt[0,1::4];

nc = ncol(xmat);
x1 = xmat[0,1::nc-1];
x2 = xmat[0,2::nc];
idx = Loc( Transpose(Vmin(Transpose(abs(x1-x2)))) == 0);

dt << New Column("t1", numeric, continuous);
:t1[idx]=1;

[#4]

Thank you for your help!

[#5 · Accepted solution]

Here is a different take on the issue.  I am using a Mode() function to see what the mode is.  If an actual mode exists, that means that at least 2 values match, and therefore column t1 will get set to 1.  See the JSL comments for a more complete explanation.

I have attached a data table that I used for testing.

Names Default To Here( 10 );

dt = Current Data Table();
New Column( "t1" );
For( i = 1, i <= N Rows( dt ), i++,
	// add a value to the matrix that is less
	// than any value in the data table.  If
	// the mode() function does not find an
	// actual mode, it returns the smallest
	// value from the matrix, so if that is
	// the value returned, you know there
	// are not at least 2 values that equal
	// each other in the columns
	If( Mode( dt[i, 0] || [-999] ) == -999,
		dt:t1[i] = .,
		dt:t1[i] = 1
	)
);

[#6]

Thank Jim!

Mode( dt[i, 1::3] || [-999] ) == -999