I have a spreadsheet of shortpayment amounts and their invoice numbers on one
spreadsheet with a column of credit amounts with their invoice numbers on
another spreadsheet. I need to match the credits written with the
shortpayments.
kinda like this
spreadsheet 1 spreadsheet 2
inv # amount date Credit #
Amount Date
122334 15.00 11-25-09 675555 15.00
12-10-09
223345 22.00 10-20-09 754444 22.00
111-25-09

naturally there is a huge list with some credits only close to inv. short
pays with some short pays with no credits etc. but if I can match the same
amounts it would be a huge time saver.

On the face of it, to meet this aspiration
.. if I can match the same amounts it would be a huge time saver ..
you can try a "basic" index/match, set for an exact match of the amounts col

Assume invoice data as posted is in Sheet1's cols A to C, data from row 2
down, while credit data as posted is in Sheet2's cols A to C, data from row 2
down. The key col is col B = amounts

In Sheet1,
In D2:
=IF(ISNA(MATCH($B2,Sheet2!$B:$B,0)),"",INDEX(Sheet 2!A:A,MATCH($B2,Sheet2!$B:$B,0)))
Copy D2 across to F2, fill down as far as required. Format col F as dates to
taste. This pulls over credit data from Sheet2 on the basis of exact "amount"
matches in col B. This extract will work if the exact match amounts are
unique for both invoice/credit data right through. If there could be
identical amounts for different invoice/credit numbers, then it fails.
Preceding helps nonetheless? hit the YES below
--
Max
Singapore
---
"newbie Annie" wrote:
I have a spreadsheet of shortpayment amounts and their invoice numbers on one
spreadsheet with a column of credit amounts with their invoice numbers on
another spreadsheet. I need to match the credits written with the
shortpayments.
kinda like this
spreadsheet 1 spreadsheet 2
inv # amount date Credit #
Amount Date
122334 15.00 11-25-09 675555 15.00
12-10-09
223345 22.00 10-20-09 754444 22.00
111-25-09

naturally there is a huge list with some credits only close to inv. short
pays with some short pays with no credits etc. but if I can match the same
amounts it would be a huge time saver.