I have 20k line items on my sheet which I need to match to reconcile my bank. There are multiple amounts of the same value. I need a formula that will create possible matches based on similar amounts that add up to zero (negative and positive)and highlight them. I have seen one thread with conditional formatting, but that didn't generate desired result and slowed down the excel program terribly. The data that needs matching is in column F of the attachment
Regards
The only items that offset in your example are +/- 644 in rows 44/45 and +/- 750 in rows 58/59. Is that what you expect?
Entia non sunt multiplicanda sine necessitate
Yes but the file i am working on has 20k line items and there would be multiple items like that and they wont always add up to zero. The intention is to match as many as possible and leave the unmatched ones unhighlighted. Will it be easier if i uploaded the original file?
You are looking for 1:1 matches, correct? If so, yes, please upload the file.
the file exceeds the allowable size limit of 1000kb, any other way I can upload the file here?
Regards
Put it on box.net or dropbox and post a link.
here is the link
https://www.dropbox.com/s/2wbc9gr1h4...CELFORUM1.xlsx
Thank you!
This takes several minutes to run. You can watch progress in the status bar.
It cancels about half of the 42K transactions. Results appear to the right of the last used column (i.e., with a blank in row 1).
Please Login or Register to view this content.
Last edited by shg; 03-17-2015 at 01:57 PM.
Thanks a Ton! That works really well. Is it possible that the macro matches items with nearest date and leaves the most recent item as unmatched?
It does match them to the nearest date, because it traverses the list from top to bottom. If you want it done in opposite order, sort by date descending.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks