matching formulas (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I have attached a small 15kb excel file to demonstrate.
I have two ranges assigned in the demo: 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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote