Formula for matching even and uneven transactions

0

Hi,

I am trying to come up with a formula to match off data but not having much luck.  I am trying to match credit card (cc) transactions to the general ledger (GL).  I combine these two pieces of data into one file, where the cc transactions show as a positve and the GL entries as a negative number.  The data includes the date of the transaction, a unique identifier (which relates to an individual), and the amount.  I envision a forumla that will match the cc transactions and the GL by marking an "x" or something.  All of the "x" rows will add to zero and the remainig rows will be the outstanding transactions that do not match.  One other caveat is that there may be multiple GL transactions that add up to the cc transaction.  They will occur on the same day and have the same unique identifier though.  For example, there may be a $142 cc charge (positve) that is offset by two $71 GL charges (negative).  I will attach a sample file.  In the sample, column D is how I would prefer the output to look but if you have a better way, I'm open to suggestions. 

Answer
Discuss

Discussion

Please don't forget to select the answer that worked best for you or ask for clarification! (Click the Select Answer button at the bottom of the desired answer to select it as the solution to your issue.)
don (rep: 1382) Oct 9, '17 at 4:08 pm
Add to Discussion

Answers

0

My answer is to get out your knife...start widdling away!  I use another column for ABS (Col. D and move the match col. to E) ~ the absolute value of the transactions.  Then I sorth on the ABS column, then on the original column (your Col. C).  Sometimes I use Numbers or letters for the Match column - this way you know what paired up with what (this sometimes helps on duplicate numbers to reconcile!  You can always filter out the Matched cells so you are only looking at what still needs to be matched.

 I believe that you have the right idea.

Discuss
0

Matching sums is immeasurably more difficult than matching numbers. I wouldn't even try to do it with worksheet formulas. Using VBA is possible but demanding.

Discuss
0

You could try using the SUMIF formula in your example if all the details for one code match they will show as zero and a mismatch value for the other details . It might at least whittle down the lines you have to look at. 

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login