1 Reply Latest reply: May 15, 2014 10:02 AM by Grant Perkins RSS

    Matching opposite

    nadineschaefer _

      I have Monarch Pro vs 8.

       

      I have two reports, one report will be a report of Buy's and the second report will be a report of Sell's and I need to match up any Sell with Buys for items that have the same CUSIP and ACCOUNT.

       

      I open both reports and I look for duplicate based on the CUSIP and ACCOUNT, but then I get stumped on how to make sure I just see the items that just have a BUY and a SELL.

       

      Report 1

      TYPE CUSIP     ACCOUNT  

      -


      -


      -


      SELL  894675      621767

      SELL  894675      568727

      SELL  894675      568716

      SELL  46113M      621767

      SELL  46113M      568727

      SELL  46113M      568716

       

       

      Second Report

                                  

      TYPE CUSIP     ACCOUNT  

      -


      -


      -


      BUY   46113M      621767

      BUY   46113M      568727

       

       

      Thank you

       

      Nadine

        • Matching opposite
          Grant Perkins

          Hi Nadine,

           

          If the report layout really is as smple as you have posted ...  AND you ojnly need records where both BUY and SELL exist ...

           

          Open one of the reports and model the fields.

           

          OPen the other report as an EXTERNAL LOOKUP to the first report. Monarch will interpret it as a space separated 'database'.

           

          Make the link using the CUSIP and ACCOUNT fields. Read in the But or sell field (depending on which file it is) or just set a flag to say there was a record to lookup.

           

          Filter for the flag or the combination of a record with both buy and sell fields populated.

           

           

          If it is a little more  complicated and the direct lookup fails, process one of the reports to give you a valid external lookup table first and then link if as a lookup to the other report as described above.

           

          HTH.

           

           

          Grant