Danny
04-19-2009, 06:35 PM
I have 2 sheets,
SHEET1 "Balance":
has a list of company names in column B and balances for different sales in column D.
SHEET2 "Payments":
has a list of company names in column H and payments for different sales in column K.
I am wanting to do a lookup to pull the payment amounts from sheet2 into sheet1 Column E.
BUT......
Companies may appear on both sheets multiple times (usually not more than 3). Meaning that they have mutiple balance amounts and multiple payment amounts, and payments often do not equal the full balance amount (but they are close).
I am trying to write a code that would match the highest payment amount with the highest balance amount, and so on. A VLOOKUP with 2 criterion can knock out a portion but not much. (wouldn't a unique ID be great !!!!! :banghead: )
Anyway, i am starting to ramble, hope this makes some sense.
The attachment has a third sheet showing the ideal outcome, but any help or ideas would be appretiated. There is a macro in module 1 called calcPMT I have been trying to use (if anyone needs a good laugh).
SHEET1 "Balance":
has a list of company names in column B and balances for different sales in column D.
SHEET2 "Payments":
has a list of company names in column H and payments for different sales in column K.
I am wanting to do a lookup to pull the payment amounts from sheet2 into sheet1 Column E.
BUT......
Companies may appear on both sheets multiple times (usually not more than 3). Meaning that they have mutiple balance amounts and multiple payment amounts, and payments often do not equal the full balance amount (but they are close).
I am trying to write a code that would match the highest payment amount with the highest balance amount, and so on. A VLOOKUP with 2 criterion can knock out a portion but not much. (wouldn't a unique ID be great !!!!! :banghead: )
Anyway, i am starting to ramble, hope this makes some sense.
The attachment has a third sheet showing the ideal outcome, but any help or ideas would be appretiated. There is a macro in module 1 called calcPMT I have been trying to use (if anyone needs a good laugh).