Results 1 to 4 of 4

Thread: Compare (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Compare (2000)

    I have 2 columns of text and data on a worksheet; the first column is text (names of locations), and column 2 are prices. On worksheet 2, I again have names in column 1, but not exactly the same names. For example, I may have 100 names on worksheet 1, and only 90 on worksheet 2, most, but not all may be on worksheet 1. How can I compare the two worksheets and determine which names are on worksheet 1 but not on worksheet 2, and include the respective prices for those not listed on worksheet 2? I would like to be able to do this for 12 worksheets, as each worksheet represents a month; hence, I would like to do a yearly summary.
    Thanks,
    Jeff

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare (2000)

    Hi There

    The attached may help. Sheet 1 contains a price list. Sheet 2 contains a lookup in column B, dependent upon value in column A, that looks for the value in Sheet 1 column A and if it finds it, retrieves the corresponding price from column B on Sheet 1. The command is show below:

    =IF(A1<>"",IF(ISERROR(MATCH(A1,Sheet1!$A$1:$A$7,0) ),0,INDEX(Sheet1!$B$1:$B$7,MATCH(A1,Sheet1!$A$1:$A $7,0))),0)

    The MATCH(A1,Sheet1!$A$1:$A$7,0) looks for the value in A1 in the list Sheet1!$A$1:$A$7, if found, this returns a number between 1 and 7 (in this scenario).

    The INDEX(Sheet1!$B$1:$B$7, part of the command looks in column B in Sheet 1 and returns the Nth item from the list, where N is this the number returned by the MATCH statement above.

    Extend the ranges to suit. You may be better creating a lookup table somewhere and then giving this a named range.

    Hope this helps.

    Regards
    Peter
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Compare (2000)

    Works great, Peter. BTW, any idea of a thread that explains how to construct a lookup table?
    Thanks again.
    Jeff

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare (2000)

    Hi Jeff

    Do a search is probably the best way to find related threads. In the meantime, I've updated the worksheet I sent you to use lists and lookups, this may give you some ideas of your won to work with. Good luck!

    Regards
    Peter
    Attached Files Attached Files

Posting Permissions

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