2 Replies Latest reply: May 15, 2014 10:12 AM by Eamon Donoghue RSS

    Excluding Duplicate Rows in Summary?

    Amadeus23 _

      This seems like it should be the easy part of my project, but I'm stumped in how to remove multiple rows of the same ID number in a Summary.  Most people have one row of information, but there are a few duplicates.  I only need one of each record to be used in formulas on the summary page. (not just hide them in a display).


      The Duplicates are children, but they all use the same ID number.


      For example:


      1.   348923  Johnson, Roger     $347.34

      2.   348923  Johnson, Roger Jr  $347.34

      3.   346379  Jones, Marsha      $124.49

      4.  ...etc.


      Need just one of the Roger Johnson records to show up in a summary.


      Any ideas would be appreciated, thanks

        • Excluding Duplicate Rows in Summary?
          Olly Bond

          Hello Amadeus,


          You'll need to use the ReferenceNumber field as the Key for your summary, and then use the Amount field as a measure. From your sample, I'm not sure whether Max(Amount), Min(), or Unique() would be the most helpful aggregation. If you include the Name field as a key or an item in your summary you will end up with to many rows.


          Another option if the children are always listed after the parent as the data is sorted in the table is to use an advanced filter instead of a summary. Create a new filter and in the Advanced tab select records that are Unique by ReferenceNumber, and for duplicates tick the box to show the first row only as sorted.


          Hope this helps



            • Excluding Duplicate Rows in Summary?
              Eamon Donoghue

              Hi Amadeus23,

              Am I correct in assuming that all of the 'Children' records contain ' Jr' at the end?  And is this consistent throughout the data?


              If so, I would suggest creating a Calculated Field where these extra characters are removed (including any spaces - Trim functions might be useful here) and the Child name is made identical to the parent name.  This will achieve the desired result.


              In any case, I think you will have to 'normalise' these names if you are to use them as a key field in a Summary.