Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    matching formulas (Excel 97)

    I have to match up two lists of invoice/amount items in a reconciliation project. I know, and have attempted to search for, posts in the Excel forum which relate to this issue, but without success. I recall reading a couple of posts last year discussing array formulas and their use in this attempt. If someone familiar with this could direct me to those threads, I'd be obliged.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: matching formulas (Excel 97)

    There are litterally hundreds of threads that discuss the use of array formulas. It would be a little tough to direct you to the right one without a little better understaning of what you need to do. My first impression from your brief description is that you need to use VLOOKUP(), not an array formula.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: matching formulas (Excel 97)

    Thanks. I thought there would be. I thought I saw something last summer. Some quick way to get around the lookup function by an array formula. I'll see if I can peruse the posts of last summer in some way to find it. Or I'll just hack away at the project.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: matching formulas (Excel 97)

    We would be glad to help if you can describe what you are trying to do.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: matching formulas (Excel 97)

    Torquemada,

    Attached is a workbook containing 1 sheet that might help. This came from a talk I gave at my PC club in January on array formulas. I did the work for this around Nov-Dec, probably stimulated in part by posts appearing at the time. Although I don't recall any particular posts, I'm sure Bob Umlas was active in them. So the material may very well be his (he has a tutorial on array formulas) but I did add additional examples.

    By the way, the material does work with numeric entries as well.

    Fred
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: matching formulas (Excel 97)

    When I do a 'reconciliation' between two lists I like to use conditional formatting.
    If I have a ListA and a ListB, I like to highlight all the items in List A which are NOT in List B with a coloured background e.g. red.
    If neccessary, I then use conditional format formulas to do the same thing in List B, i.e. in List B, change the background for those items which are NOT in List A.
    Then all of the items which have'normal' backgrounds have corresponding entries in both lists.

    This is straightforward if you are checking simple items e.g. a list of account numbers in List A against a master list in List B - then the highlighted cells in List A represent 'new' account numbers (or 'unrecognized' account numbers)

    zeddy

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: matching formulas (Excel 97)

    Zeddy,

    I'm not the original poster on this so I hope they saw this too. But I'd be interested in your solution - can you post it?

    Fred

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: matching formulas (Excel 97)

    I have attached a small 15kb excel file to demonstrate.
    I have two ranges assigned in the demo: [list] on separate sheets.
    If you edit any of the entries that are 'matches' - i.e. change the last digit so that it no longer matches, you'll see an instant update to the background.
    This technique is great for 'spotting errors'

    zeddy
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: matching formulas (Excel 97)

    Thanks for the responses! Things have been hectic and I'm just checking back in. The conditional formatting solution is a good starting point, but I deal in money. Suppose there were dollars in the columns next to the invoice numbers. Can I sum the highlighted invoice lines on each tab to some location, to determine the dollar value of the highlighted items?
    thanks again.

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: matching formulas (Excel 97)

    Building upon the worksheet of Zeddy, I added some columns and formulae that produced what I needed. So thanks for getting me started! The modified file is attached. Any comments would be appreciated.
    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
  •