Unique Filter from one spreadsheet to another

DDT123

New Member
Joined
Aug 9, 2011
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Greetings - I have 2 worksheets, worksheet one has a list of contract numbers in column A, and worksheet two has a list of contract numbers in column B. On a blank worksheet (worksheet three), I'm needing the formula which will compare worksheets one and two, then list the contract numbers from spreadsheet two which weren't listed on spreadsheet one.

Thanks in advance.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Perhaps this:
Sheet 1:
Book1
A
1Contract #
2352
3821
4487
5123
6420
7838
8378
9598
10734
11864
12843
13260
14638
15112
16726
17518
18758
19410
20356
21234
Sheet1


Sheet 2 (col C for reference to show which numbers do not match sheet 1):
Book1
ABC
1Contract #
2477#N/A
38212
44873
5778#N/A
6148#N/A
7252#N/A
81234
9581#N/A
10568#N/A
11963#N/A
1241018
1335619
1423420
15419#N/A
16235#N/A
17781#N/A
18795#N/A
1911214
20406#N/A
21168#N/A
Sheet2
Cell Formulas
RangeFormula
C2:C21C2=MATCH(B2,Sheet1!$A$2:$A$21,0)


Sheet 3:
Book1
A
1Contract #
2477
3778
4148
5252
6581
7568
8963
9419
10235
11781
12795
13406
14168
Sheet3
Cell Formulas
RangeFormula
A2:A14A2=FILTER(Sheet2!B2:B21,ISNA(MATCH(Sheet2!B2:B21,Sheet1!$A$2:$A$21,0)))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top