topic Re: find continuous empty cells in Discussions

find continuous empty cells

dadawasozo — Fri, 04 Apr 2025 22:28:07 GMT

Hi,

is there a way to find continuous empty rows like below? is there any function in JMP that can quickly finding specific number of continuous empty rows? example in picture. in the same column there is multiple rows that is empty but I only interested with the 3 continuous rows that is empty.

Re: find continuous empty cells

txnelson — Sat, 05 Apr 2025 01:44:13 GMT

I did this by adding 2 formula columns, Column 2 and Column 3

Column 2's formula is

As Constant( count = 0 );
If( Is Missing( :Column 1 ) == 1 & Is Missing( Lag( :Column 1 ) ) == 0,
	count
	++);
If( Is Missing( :Column 1 ) == 0,
	.,
	count
);

and Column 3's formula is

If( Col Number( :Column 2, :Column 2 ) == 3,
	3,
	.
)

Re: find continuous empty cells

jthi — Sat, 05 Apr 2025 12:08:45 GMT

Matrix operations can also be used for this, but usually it isn't necessary and you can just use formulas. txnelson provided one very easy to understand version, here is other version

Column C

Col Cumulative Sum(!IsMissing(:Column 1)))

Column N

Col N Missing(:Column 1, :C))

If(IsMissing(:Column 1), Col N Missing(:Column 1, :C),.))

Can combine these into single one, but these tend to be difficult to understand

If(Is Missing(:Column 1), 
	Col N Missing(:Column 1, Col Cumulative Sum(!Is Missing(:Column 1)))
	, .
);