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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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