Results 1 to 8 of 8
Thread: Compare Lists (2000)

20031118, 15:22 #1
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20031118, 15:36 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20031118, 16:32 #3
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031118, 16:38 #4
 Join Date
 Jan 2001
 Posts
 1,119
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031118, 16:58 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Compare Lists (2000)
If you want leading zeroes, you either have to:
make it text and add the leading zeroes [start with a space or an apostrophe(')]
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

20031118, 17:02 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Compare Lists (2000)
I think you mean the Value function not the number function.
=value(A1)
There is no number function.
Steve

20031118, 17:04 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Compare Lists (2000)
Yep, thanks. I will modify my reply.

20031118, 17:05 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.