3 Replies Latest reply: May 15, 2014 10:11 AM by Olly Bond RSS

    Filtering out almost duplicate lines

    Kamawell2 _

      I've got several reports that occasionally include an entry twice, once with an extra piece of information:

       

      999 99999 Qwertyuio Support Asdf  12345678-2         Anon, Mr. Anon (Anon)     23-MAY-09             Protected Amount                                                              1234.56                                                                               

      Reason                                                      Change of Shift Pattern                                                                               

      Period Cash Amount                                                            789.99                                                                               

      Date Worked                                                                               

      STP Pay                                                                             123.99                                                                               

      Pen Prot                                                                               

      999 99999 Qwertyuio Support Asdf  12345678-2         Anon, Mr. Anon (Anon)                         Protected Amount                                                              1234.56                                                                               

      Reason                                                  Change of Shift Pattern                                                                               

      Period Cash Amount                                                            789.99                                                                               

      Date Worked                                                                               

      STP Pay                                                                             123.99                                                                               

      Pen Prot   /CODE                                                                               

      The vast majority of entries don't have the extra date included but I can't filter to exclude those that do in case there's one that isn't accompanied by an entry that doesn't have it.

      My model currently captures the 12345678-2 (which should be unique for each individual,) the name, the date if it's present and the STP Pay amount. 

      I'd like to end up with a summary that only includes one of these entries but can't think of a way to get there.  Does anyone have any suggestions?

       

      Thanks

       

      Andy

       

      Edit: Can't quite get the sample right - looks good in the preview but not so good posted!

        • Filtering out almost duplicate lines
          elginreigner _

          If 12345678-2 is unique, you only need to create a filter to show unique values or 1 duplicate. Open filters in Monarch, create a new filter, select the advanced tab, "select rows according to their uniqueness", check off Unique rows and Duplicate rows but only "first row only, as sorted". This will remove the duplicate enties, however, if some of the duplicate entries are not identical, i.e. name is missing in one but not the other, adjust your filter to accomodate these.