3 Replies Latest reply: May 15, 2014 9:58 AM by Mike Urbonas RSS

    Listing Duplicate items only

    glregez _

      I have created a table with inventory locations and item numbers.  I need to identify the items in location that are similiar. For this dicussion lets say if the first 3 digits of the item number are equal we will display the line. Its kind of the opposite of eliminating duplicate records, I want to only list the duplicates.

       

      example  (Table)

      Location      Item

         1         100-1

         1         200-1

         2         100-1

         2         100-2

         2         200-4

       

      The report should only show the location 2 with the 100-1 and 100-2  Duplicate item (first 3 digits) at this inventory location.  This is my first attempt at Monarch. I'm using V5.

        • Listing Duplicate items only
          Mike Urbonas

          The Summary window of Monarch can do this for you.  Your example is a little tricky because you want to match records based on Location plus[/b] the first three characters of the Item.

           

          So I would start by creating the following Calculated Field in your Table, to bring the Location and first three characters of the Item into one field (we'll call it Location and Item Code):

           

          Location+LEFT(Item,3)

           

          which will result in a column as follows:

           

          Location and Item Code

          1100

          1200

          2100

          2100

          2200

           

          Now, go into the Summary window, click Edit -> Define Summary.  (BTW, if you have never created a summary in Monarch before, take some time and look over the online help - Chapter 3 The Summary Window.  As its name suggests, Monarch's Summary window takes all the data from the Table window and summarizes it together in some useful way for you.)

           

          Next, Select "count" as your Summary Field and select the following fields as Key fields IN THIS ORDER (from first to last):

           

          Location and Item Code

          Location

          Item

           

          Then, double click on Location and Item Code under "Key Field."  Click on the Matching tab.  Click on the following radio buttons:

           

          -- Summary Field

          -- is at least

          and then type in the number 2 next to that.

           

          What did we do?  We are telling Monarch to build a summary showing the data lines in which the Location and Item Code count up to a total of 2 or more (ie, a duplicate).  The ones that are not duplicates are rolled up into a separate "All Others" category (you can change the name of this category if you want, BTW).

           

          Finally I recommend clicking on the Display tab next and check off the following boxes:

          -- Suppress duplicate values

          -- Insert subtotal line when key value changes

          -- Insert blank line when key value changes

           

          and click OK all the way out.  This is what your Summary should look like:

           

          [font="courier"]Location and Item Code  Location    Item              count

          2100                    2           100-1                 1

                                  2           100-2                 1

          Subtotal                                                  2

           

           

          All Others              1           100-1                 1

                                  1           200-1                 1

                                  2           200-4                 1

           

          Subtotal                                                  3

           

          Summary                                                   5 /font[/quote]Let me know how this works for you.

          Mike

           

          [size="1"][ November 14, 2002, 01:49 PM: Message edited by: Mike Urbonas ][/size]

          • Listing Duplicate items only
            glregez _

            Worked great, but I bet you can figure out what the next question I got when I showed it to the users.  How do I get rid of the "all others" lines on the report.

             

            Does Monarch have a filter that can be set up after the summary?  Thanks again.  Gary

            • Listing Duplicate items only
              Mike Urbonas

              Originally posted by glregez:

              Worked great, but I bet you can figure out what the next question I got when I showed it to the users.  How do I get rid of the "all others" lines on the report.

               

              Does Monarch have a filter that can be set up after the summary?  Thanks again.  Gary[/b][/quote]Your next question was a complete surprise to me!         

               

              Supressing the All Others category is not currently available in Monarch.  Most of the time the "All Others" category is useful because the grand total at the bottom provides a "tie" back to the original report and confirms the summary is indeed using all of the report data.

               

              Workarounds include highlighting only the desired portion of the summary, leaving out the All Others category, then copy and paste that portion to Excel, or, if you are automatically exporting the summary into say an Excel workbook, run a VB script to automatically delete the "All Others" part of the summary.  Lastly you could save the summary in its entirety as Excel, Access etc. and open that file in Monarch V6 Pro (File -> Open Database command), and you could then define a table filter to remove the All Others lines.

               

              Mike

               

              [size="1"][ November 18, 2002, 01:21 PM: Message edited by: Mike Urbonas ][/size]