53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.If I have hundreds of rows, each row is a separate work ticket, each row can have up to 53 time columns filled in, but most only have some of these columns filled in and ones in between are blank.
So say for Row #1: I have column 1, column 23 and column 36 are the only columns containing a time stamp, I will need know that 36>23 and 23>1.
For Row #2 all columns have time stamps so I will need to know that [53>52,............,2>1].
Thank you
Fiona
Data TableRe: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.Just to clarify; They should all be correct so I'm thinking the easiest way is to have a flagging column that will display a "1" at end of any row where above is not the case.
Re: 53 Time columns, some empty and some filled. How do I write a formula ensuring the highest filled column is > than next filled column etc.I am not exactly sure that this is what you want, but the script below scans across the columns and makes sure that all date values are increasing from the previous date. If not, it places the name of the column that has detected an earlier date than the previous date.
Names Default To Here( 1 );
dt = Current Data Table();
If( Try( dt:flag << get name, "" ) == "",
dt << New Column( "Flag", Character )
);
For( rowNum = 1, rowNum <= N Rows( dt ), rowNum++,
For( startCol = 1, startCol <= N Cols( dt ), startCol++,
If( Is Missing( Column( startCol )[rowNum] ) == 0,
holdVal = Column( startCol )[rowNum];
Break();
)
);
For( col = startCol + 1, col <= N Cols( dt ) - 1, col++,
If( Is Missing( Column( Col )[rowNum] ) == 0,
If( Column( Col )[rowNum] < holdVal,
dt:Flag[rowNum] = (Column( Col )) << get name;
holdVal = Column( col )[rowNum];
Break();
,
holdVal = Column( Col )[rowNum]
)
)
);
);
