Matching numbers

[steve263]

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
	A	B	C	D
1	75		75
2	94		123
3	123		465
4	222		555
5	225		871
6	465
7	468
8	555
9	735
10	871
Sheet1

 

[Peter_SSs]

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]

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]

Hi

Not so elegant but effective!

Eli

Book1 (version 1).xls
	A	B	C	D	E	F
1	75		75
2	94		123		94
3	123		465
4	222		555		222
5	225		871		225
6	465
7	468				468
8	555
9	735				735
10	871
11
data

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H
1	List 1		List 2		No. Unmatched		Unmatched Numbers
2	75	0	75		5		94
3	94	1	123				222
4	123	1	465				225
5	222	2	555				468
6	225	3	871				735
7	465	3
8	468	4
9	555	4
10	735	5
11	871	5
12
Compare Lists

 

[steve263]

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]

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