Complicated Matching criteria, with autonumbering the match

dinesh.sarsar

New member
Joined
Sep 8, 2014
Messages
21
Reaction score
0
Points
0
Location
India, Maharashtra Pune
Excel Version(s)
MS OFFICE Windows 10
Hi,

We have to reconcile a monthly stock rec file.

The matching criteria is as follows

1) The match has to be E v/s as given in column B.
2) The match has to be a positive units v/s negative units as given in column C.
3) The match has to be as per the Trans Type i.e
a) Transfers In v/s Transfer In - Inspecie
b) Sell v/s Repurchases
c) Buy v/s Sales
4) The only exceptional match is I v/s I as given in column B i.e Transfer In - Inspecie - Cancelled v/s Transfers In
as it is a cancelled deal.
5) The matched items are to be given a match number as given in column F, the number has to be same for the match.

Also Fractional difference for the match has to be excluded.

I hope i have put the question in the most best way that i could have.
 

Attachments

  • NFUM Monthly Stock Rec.xls
    15 KB · Views: 20
Comparing your post against the attachment, No the question is not clear.

Can you attach a workbook with a before sheet and an after sheet? Please "dummiefy" any personal or proprietary information.
 
I agree with SamT that your question is not very well explained. I can see that you are trying to match transactions, and that there is a positive and negative element making up each transaction.
What seems to be missing, and therefore making the exercise complicated, is a transaction code (common to both elements) that matches them automatically. Different transaction types (e.g. Cancel or Sale) can be catered for by using either
a separate type reference, or making it part of the transaction code. These will probably be in your system in some form, as without them you wouldn't be able to operate basic business functions properly.
 
Hi,

I have attached the before and after file.

Just to make my query more clear.

We get around 500 to 700 entries in this format, we match the internal records with external records with the amount. Then we number the match in series lilke 1,2,3.....and so on.

What i generally do is I round up the unit amount and remove the negative sign, then use match function and put the match numbers manually. This takes a lot of time, please check if we can match the unit amount with the trans ref as mentioned below.

a) Transfers In v/s Transfer In - Inspecie
b) Sell v/s Repurchases
c) Buy v/s Sales

And an only exceptional match is I v/s I as given in column B i.e Transfer In - Inspecie - Cancelled v/s Transfers In
as it is a cancelled deal.

Please help me out of this.
 

Attachments

  • Before File.xls
    14.5 KB · Views: 16
  • After file.xls
    15 KB · Views: 17
I looked over your examples. I modified the After Example by adding a Helper column and some colors. (See Attachment.

These are the Criteria I see:
  • For every Transfer Type "Trans*" with (+) value; Find Matching, within "Sensitivity," (-) Value in Transfer Type "Trans*
  • For Every Transfer Type "Repurchases"; Find Matching Value, within "Sensitivity," in Transfer Type "Sell"
  • For Every Transfer Type "Buy"; Find Matching Value, within "Sensitivity," in Transfer Type "Sales"
  • Insert Match Number for both transactions
  • On every match, increment Match number by 1
  • It doesn't matter which particular items are matched as long as they are in the correct Transfer Types.

Cannot use Rounding (see blue cells in attached), must use +- Values (Sensitivity.)
 

Attachments

  • After file.xls
    15.5 KB · Views: 14
It might be me, but it seems that there remains a problem automating this process, as you still need to deternine the matches (manually) in order to sort the records correctly (ie match col = primary sort). Also, although ABS brings together like numbers, a correct match on values needs to be a positive and a negative of the same value. Sorting by ABS will not do this.
Can I ask:
If I focus on two records that match, (say match 1) then because these records have compensating values, one of these must be raised as a consequence of the other, so why can't they be given a common reference/partial reference, which will solve your problem?
 
Back
Top