compare formula to find no match?

JWARD

Board Regular
Joined
Aug 6, 2004
Messages
137
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
ABCDEFGHIJKL
195422011/17/04NAM1767022JeffDenker964814YG433212
295592812/09/04YG8555811JeffWard955928YG855581
3956266KEO2706088JeffDenker964986YGAK2101
496456401/19/05YGAK02144JeffDenker964993H721202
596456401/19/05YGAK21444JeffDenker954220NAM176702
696456401/19/05YGAK21733JeffDenker964982CA1587510
796456401/19/05YGAK01433JeffDenker964982CA4995810
8964814YG4332122thanksforyourorder,Donn96498710310145210
9964910PB1544JeffDenker96498710310500210
10964921ST726020100100LOREN924120EAG1
11964921ST726029100100LOREN734837G993
12964921ST726034100100LOREN734837G993
13964921ST726045100100LOREN956266KEO270608
14964921ST726019100100LOREN964924LTR375121
Sheet2
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Aug 6, 2004
Messages
137
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.
 

Forum statistics

Threads
1,147,674
Messages
5,742,541
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top