Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    5 Star Lounger
    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.

  4. #4
    5 Star Lounger
    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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare Lists (2000)

    Yep, thanks. I will modify my reply.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Posting Permissions

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