Evaluate duplicate data on same column

Question

Hi all,

I have these duplicate data where I need to evaluate them as one. Is it possible to evaluate data as one on the same column? I have this example. If column1 (same data value) has different column2 values, column3 result should be Outlier and be counted as one and not in duplicate values like column1. But if their column2 has the same "Good" values, column3 value will be "good".

Is this evaluation possible?

Column 1      Column 2     Column 3
5631823522     Good          Outlier
5631823522     Outlier        
563180572E    Good          Good
563180572E    Good          
5631826915    Outlier        Outlier
5631826915    Good

txnelson · Accepted Answer

Here is one way of handling this.  The below is a formula  for Column 3

If( Row() == 1 | :Column 1 != Lag( :Column 1 ),
	theColumn = :Column 1;
	theRows = Current Data Table() << get rows where( :Column 1 == theColumn );
	x = "Good";
	For( i = 1, i <= N Rows( theRows ), i++,
		If( :Column 2[theRows[i]] == "Outlier",
			x = "Outlier";
			Break();
		)
	);
	x;
)

jthi · Accepted Answer

Many ways to handle this. Writing one formula like @txnelson suggested, or you could include a few more steps and have a bit simpler formula with extra step

1. Recode Column 2 in such a way that Good is 0 and Outlier 1 (can use JMPs Recode for this), make sure new column is numeric

2. Use formula which will calculate sum (or max value) of the data in the new column (this could be also done with Summary and join or many other ways)

If(ColSum(:"Column 22"n, :"Column 1"n) >= 1,
	"Outlier",
	"Good"
)

3. Then depending on what you want to do with your data, you could remove duplicates or just the values. Select columns Column 1 and Column 3 and then use JMPs select duplicate rows

4. From here you could delete the rows

5. Or if you want to keep the duplicated values but just clear the Column 3 values. While holding alt press on Column3 header so you have only Column3 selected

and press delete