Delete Duplicate rows

[#1]

Hi,

I have a datable that I want to delete rows on a duplicate value in a col. 

Thank you,

Rami

[#2 · Accepted solution]

Select Duplicat Rows() function will highlight rows for a specified column(s)

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt <<
Select duplicate rows( Match( :name ) );
xx=dt<<get selected rows;
if(n rows(xx)>0,
	dt<<delete rows;	
);

[#3]

Hi txnelson!

I just wanted to say, I keep seeing your solutions whenever I look up JMP help, and I really appreciate all the work you've done helping people out. You've saved me a ton of time and helped me understand this software better, and it's really appreciated.

Thanks!

A happy JMP user

[#4 · Accepted solution]

Hi Rami,

Jim's script is a nice option, but if you have JMP 14 you can also select duplicate rows in a column by point and click.  

Highlight the column of interest, go to the Rows dropdown or red hot spot.  Rows > Row Selection > Select Duplicate Rows.  All of the duplicate values will be highlighted in by row number.  Right click anywhere in the blue region and select delete.  

HTH

Bill

[#5]

It is recommended that JMP continue to improve to enable all automation with JSL.Thank you very much!

[#6]

I have a data table which has column called Part_number.I need to remove duplicate part_numbers.The delete duplicate rows options suggested above deosnt work as in no rows get selected.The column is character,nominal type.I'm using JMP12.

Any other options that I can try?

Also does this function retain the first row and delete the repititions as in Excel?

Thanks

[#7]

The Select Duplicate Rows() function was not in JMP 12.  The below code should do what you want

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\Big Class.JMP" );

dt << sort( by( :Part_number ), order( ascending ), Replace Table( 1 ) );
dt << New Column( "dup",
	formula(
		If(
			Row() == 1, value = 1,
			:Part_number != Lag( :Part_number ), value = 1,
			value
			++);
			value;
	)
);

dt << select where( :dup > 1);

dt << delete rows;

dt << delete column( "dup" );

[#8]

Thanks Bill for the tip/trick.

I sometimes have duplicate rows but struggle how to delete them. Just thinking about JMP Community and it helps to solve the issue.