Results 1 to 6 of 6

Thread: Complicated Matching criteria, with autonumbering the match

  1. #1

    Complicated Matching criteria, with autonumbering the match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    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.

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    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.

  4. #4
    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.
    Attached Files Attached Files

  5. #5
    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.)
    Attached Files Attached Files

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •