How to match values for two columns while order could be different

[#1]

Say I measured defect for a wafer twice, and have X, Y axis data for each defect.

Some defects are repeat defect, they have very similar axis data (although not exact the same), so I would put "repeat" tag for them, while some defect do not have match, so I would put "new" tag for them.

How do I write a formula to automatically do this? (ie: formula to auto make column "judge base on after") 

(Note that for same defect, values are not exact the same, just very close. And same defect could be in different row for before & after)

X-Before	Y-Before	X-After	Y-After	judge base on after
-446659	888856	-446649	888852	Repeat
-445908	630775	-445899	630772	Repeat
-447462	301534	-446122	588199	new
-140914	508425	-447456	301532	Repeat
		-140907	508433	Repeat

[#2]

Here is one approach.  I determines the difference between the two points, and then applies the New/Repeat logic to the difference.  I believe you have an error in your data table, that shows up in the new calculation

Names Default To Here( 1 );
dt = Current Data Table();

xFudge = Abs( Min( Col Min( :"x-before"n ), Col Min( :"x-after"n ) ) );
yFudge = Abs( Min( Col Min( :"y-before"n ), Col Min( :"y-after"n ) ) );
//dt << New Column( "diff" );
dt << New Column( "Repeat_New", character );

For Each Row(
	diff = Sqrt(
		((xFudge + :"x-before"n) - (xFudge + :"x-after"n)) ^ 2 + ((yFudge + :"y-before"n) - (yFudge +
		:"y-after"n)) ^ 2
	);
	If( diff < 100 | isMissing(diff)
	,
		:Repeat_New = "Repeat";
	,
		:Repeat_New = "New"
	);
);

[#3]

Hi txnelson,

Thank you for your reply, I tried your method. However, there is a problem.

It seems you only compare data at same the row.

The real situation is:

For before, there could be 500 defects, ie 500 rows. For after, there could be 1000 rows.

Same defect may locate at row 50# in before column, and locates in row 121 in after column, you won't know.

So it is necessary to compare all 1000 rows of after, with all 500 rows of before to match every repeat defect.

The match result may be: 490 among 500 could find a match, 10 were missing, and the other 500 are new adder 

[#4]

Thank you for the clarification.  The key to this script is the understanding of the function

     dt << get rows where()

And then looping through all of the rows that have valid Before X and Y values.

Names Default To Here( 1 );
dt = Current Data Table();

xFudge = Abs( Min( Col Min( :"x-before"n ), Col Min( :"x-after"n ) ) );
yFudge = Abs( Min( Col Min( :"y-before"n ), Col Min( :"y-after"n ) ) );
dt << New Column( "Repeat_New", character, set each value( "New" ) );

For( i = 1, i <= N Rows( dt ), i++,
	If( Is Missing( :"x-before"n[i] + :"y-before"n[i] ) == 0,
		Try(
			:Repeat_New[dt << get rows where(
				Sqrt(
					((xFudge + :"x-before"n[i]) - (xFudge + :"x-after"n)) ^ 2 + ((yFudge
					+:"y-before"n[i]) - (yFudge + :"y-after"n)) ^ 2
				) < 100 & Row() >= i
			)] = "Repeat"
		)
	)
);

Given your estimate of how many rows your data table may have, you can expect the script to take a significant amount of time to process.

My use of <= 100 units as the distance considered to be the same defect, is just my guess.  I have no real way to set that value, given that I do not know the resolution of the defect measurement tool and your stepper resolution capability.  But, you should be able to estimate the tool variability and from that setting the distance units.