compare formula to find no match?

[JWARD]

this data is in sheet2 , i need to write a formula or macro in sheet1 that will compare sheet2 columns a & c against columns j & k if a match is found do nothing, if no match is found bring back all unmatched values in a or a & c. how would i go about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	954220	11/17/04	NAM17670	2	2	JeffDenker				964814	YG43321	2
2	955928	12/09/04	YG85558	1	1	JeffWard				955928	YG85558	1
3	956266		KEO27060	8	8	JeffDenker				964986	YGAK210	1
4	964564	01/19/05	YGAK021	4	4	JeffDenker				964993	H72120	2
5	964564	01/19/05	YGAK214	4	4	JeffDenker				954220	NAM17670	2
6	964564	01/19/05	YGAK217	3	3	JeffDenker				964982	CA15875	10
7	964564	01/19/05	YGAK014	3	3	JeffDenker				964982	CA49958	10
8	964814		YG43321	2	2	thanksforyourorder,Donn				964987	103101452	10
9	964910		PB15	4	4	JeffDenker				964987	103105002	10
10	964921		ST726020	100	100	LOREN				924120	EAG	1
11	964921		ST726029	100	100	LOREN				734837	G99	3
12	964921		ST726034	100	100	LOREN				734837	G99	3
13	964921		ST726045	100	100	LOREN				956266	KEO27060	8
14	964921		ST726019	100	100	LOREN				964924	LTR37512	1
Sheet2

 

[gnaga]

Is this the one you are expecting?

Book1
	A	B	C	D
1	954220		NAM17670
2
3	956266		KEO27060
4	964564		YGAK021
5	964564		YGAK214
6	964564		YGAK217
7	964564		YGAK014
8	964814		YG43321
9	964910		PB15
10	964921		ST726020
11	964921		ST726029
12	964921		ST726034
13	964921		ST726045
14	964921		ST726019
Sheet1

GNaga

 

[Aladin Akyurek]

If this is a one-shot operation, you could run Advanced Filter with a computed criterion to pull off the non-matching records. The formula for the computed criteria would be something like:

=ISNA(MATCH(A2,J:J,0))

Note that, for the foregoing set up to work, you need to insert a new row at the beginning with distinct labels.

 

[JWARD]

nope this is something i have to compare on a regular basis, and alot more records then are shown in the example. the example gnaga did isnt what i need, the data he brought back on row 1 is located on row 5 j&k, so that is the data i dont want.