reconising matching no's in 2 seperate lists

  • Thread starter Thread starter ian123
  • Start date Start date
I

ian123

Hi,

I have this problem, can anyone recommend anything that will make m
life easier...

I have two lists of numbers (lets call them list A and list B) and
want to run a macro that will match the two up nicely!

The totals of the two lists can be equal or they may not be equall - i
differs each time!

A number in list A may match a number in list B directly.
Alternatively the sum of some of the numbers in list A may equal on
number in list B (and vice versa). Occasionally the sum of severa
numbers in list A may match a sum of numbers in list B.
Finally some numbers will have no match in the other list.

Is there a macro or other method within excel that will identify thes
matches, potential matches etc... or is this something beyond it
(albeit impressive) capabilities?

As you can imagine i have been working on this for quite some time
the only result being that my head, much like my macros, no longe
work!

Many thank
 
for a limited size list, you can use solver to do this.

You would have to treat each value in B as a separate problem which could
result in a wrong overall answer. I haven't seen any suggestions on how to
optimally allocate the numbers in A to the numbers in B or even that that is
appropriate. I assume you are trying to match payments/partial payments
with debits or something like that. Perhaps you could solve it as a linear
programming problem, but I would imagine the lists would have to be pretty
small to solve it in Excel.
 
Tom,

Many thanks for your consideration of my problem. I am trying to match
month end customer account balances to a list of payments made by all
payments- not the easiest task in the world!!!

Could you elaborate on 'solver' please as i do not know what this is,
its potential applications or how i would use it!

Many thanks
 
Solver is an addin shipped with Excel. It is used to solve equations. If
it is installed, you can select it in tools=>Addins, then in Tools you will
have the option for solver. One use is for solving linear programming type
problems. Excel is distributed with a samples workbook (I believe) that
should have some solver examples. If you don't see it in Tools=>Addins,
then you need to go back to office setup and install it.


Here are some previous posts on the topic: (see last url for a sample
workbook that does the first technique (or something similar)


Solver - one combination for a sum
http://groups.google.com/[email protected].
gbl

solve for all combinations (VBA)

http://groups.google.com/[email protected]&
rnum=3
(error on the column protection)

Jim Day has a download that represents the technique (not written by Jimmy)

http://www.cableone.net/twodays/recurs.xls
 
Back
Top