Matching numbers

steve263

New Member
Joined
Nov 13, 2005
Messages
20
Hi all,

I have not been around for a while as I have been busy in work, but I have a question if it could be answered. I often have to compare two sets of numbers. They are just list of pay numbers, and very often one will not appear on the other. I want to make a list out of the ones that do not appear on one list. Does that make sense?

At the moment, I put the two list side by side, the manually delete the ones that appear on both lists and the data filter the one that I have deleted from and choose 'non blanks'. Both list are of differing lengths of that helps, although I could make them the same by adding 'zeros'.

There must be a easier way, and I have tried a couple of formulas, but without success.

I had forgotten about the code maker, and have added the trial page below
Pay number test.xls
ABCD
17575
294123
3123465
4222555
5225871
6465
7468
8555
9735
10871
Sheet1
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,561
Office Version
  1. 365
Platform
  1. Windows
steve263

In your sample data, all entries in the second list appear in the first. Is that always the case? That is, is the column C list always a subset of the column A list?
 

steve263

New Member
Joined
Nov 13, 2005
Messages
20
That would be correct. The numbers on C will always appear on A. What I want is a list of numbers from A that are left after taking the ones from C away from them.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,561
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is another possible way.

1. B2 (copied down): =IF(ISNUMBER(MATCH(A2,C:C,0)),B1,B1+1)
2. E2: =LOOKUP(9.99999999999999E+307,B:B)
3. G2 (copied down): =IF(ROWS(G$2:G2)>$E$2,"",INDEX(A:A,MATCH(ROWS(G$2:G2),B:B,0)))
Mr Excel.xls
ABCDEFGH
1List 1List 2No. UnmatchedUnmatched Numbers
275075594
3941123222
41231465225
52222555468
62253871735
74653 
84684 
95554 
107355 
118715
12
Compare Lists
 

steve263

New Member
Joined
Nov 13, 2005
Messages
20
Thank you both very much. I am going to try Ellie's solution when I a back in work tomorrow. Peter, yours is excellent, but Ellie's is simpler, and I am a simple minded person!
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Try Advanced Filter.

List Range : A1:A10 (Including Header row)

Criteria range E1:E2

Copy to G1

in E2,

=ISNA(MATCH(A2,$C$2:$C$6,0))

HTH
 

Forum statistics

Threads
1,137,332
Messages
5,680,867
Members
419,937
Latest member
Talic

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