# Matching numbers

#### steve263

##### New Member
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.

Pay number test.xls
ABCD
17575
294123
3123465
4222555
5225871
6465
7468
8555
9735
10871
Sheet1

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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.

#### eliW

##### Well-known Member
Hi

Not so elegant but effective!

Eli
Book1 (version 1).xls
ABCDEF
17575
29412394
3123465
4222555222
5225871225
6465
7468468
8555
9735735
10871
11
data

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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
Hi,

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

Replies
3
Views
337
Replies
6
Views
418
Replies
8
Views
822
Replies
6
Views
492
Replies
2
Views
257

1,171,579
Messages
5,876,279
Members
433,191
Latest member
Rect0425

### 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.

### Which adblocker are you using?

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

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