Results 1 to 10 of 10
Thread: matching formulas (Excel 97)

20020413, 14:12 #1
 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.

20020413, 16:07 #2
 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

20020413, 16:19 #3
 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.

20020413, 16:23 #4
 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

20020414, 13:19 #5
 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 NovDec, 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

20020415, 10:48 #6
 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

20020415, 11:38 #7
 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

20020415, 15:10 #8
 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

20020426, 02:37 #9
 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.

20020428, 16:58 #10
 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.