1. ## Compare Lists (2000)

Is there a way to compare two lists - a long list of numbers and a short list of numbers and find out what numbers are missing from the short list?

2. ## Re: Compare Lists (2000)

Say that you short list is in D110, and the long list is in A1:A1000. Enter the following formula in E1 (insert an empty column, if necessary):

=ISNA(MATCH(D1,\$A\$1:\$A\$1000,0))

and fill down to D10. The MATCH function tries to find the value of D1 in A1:A1000; if found, it returns the index of the first occurrance, otherwise, is returns #N/A. The ISNA function changes this to FALSE / TRUE. If a cell in column E is TRUE, the number to the left of it does not occur in A1:A1000. The reference to D1 is relative, so that it will change to D2, D3 etc. when filling down; the reference to A1:A1000 is absolute, because it must remain the same when filling down.

3. ## Re: Compare Lists (2000)

I tried the formula but it just gives me TRUE. If the number is not in the long list it should come back TRUE. The number is in both lists and I get TRUE all the way down. I tried it on a short long list and a short list and it still does the same thing.

4. ## Re: Compare Lists (2000)

I think I know what the problem is. One set of numbers is text. The other set I copied in from Access and leading 0s were deleted. I used the formula "000"# to add in the missing 0s.

5. ## Re: Compare Lists (2000)

If you want leading zeroes, you either have to:

Keep it as a number and set the format to add leading zeroes.

In either case, both sets must either be text or numbers to work for Hans' formula to work.

If the short list is a number and the big list is Text you could use:
<pre>=ISNA(MATCH(text(D1,"0"),\$A\$1:\$A\$1000,0)) </pre>

Change the format in the text function to match the formatting in col A

If the short list is text (that looks like a number) and the big list is numbers you could use:
<pre>=ISNA(MATCH(value(D1),\$A\$1:\$A\$1000,0)) </pre>

Steve

6. ## Re: Compare Lists (2000)

I think you mean the Value function not the number function.

=value(A1)

There is no number function.

Steve

7. ## Re: Compare Lists (2000)

Yep, thanks. I will modify my reply.

8. ## Re: Compare Lists (2000)

Either add an extra column next to the text column, with formulas of the form =VALUE(A1), or add a column next to the number column, with formulas of the form =TEXT(A1,"0000"). Use this column to compare to the other column.

Thanks to <!profile=sdckapr>sdckapr<!/profile> for pointing out an error in the original version of this reply.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•