7 Replies Latest reply: May 15, 2014 9:57 AM by Dee Moore RSS

    trying to label duplicates

    rebecca _

      I want to identify duplicate rows in my report.  Is there a way to say, as in excel,

      =IF(A2=A1, "duplicate"), perhaps using rowno?


      I would like to say if the account number in row 1 = account number in row 2, then "duplicate".  Thoughts?

        • trying to label duplicates
          Data Kruncher

          Hi Rebecca.


          If you're just trying to have duplicate rows "pop out" a bit, rather than creating a formula in a new column, I like to use conditional formatting.


          For instance, if I'm consolidating multiple submissions of the same type of data from many source, I'll combine them all then sort the rows on the account number. Supposing that your data starts in A2, select the account number cells from A3 on down to, say, A1000. Now select Format, Conditional Formatting...


          Change the dropdown to "Formula is" and make the formula =(A3=A2) and change the Format to have a red Pattern color, and OK it.


          Now all of duplicated account numbers will appear with a red background, which stand out nicely.



          • trying to label duplicates
            Grant Perkins

            Hi Rebecca,


            Following on from Sandy's suggestion, if you output the fields you require in a summary you could then use the "suppress duplicate values" for the account number field (must be a key field and sorted by the required key for this to work).


            If you then look at Colouring properties for the field ( I am assuming you have a version of Monarch with these features ...) you can set a revised cell colour combination for the key fields and choose NOT to set the suppressed duplicates the same colour - which would help to make them stand out.


            If you need to see the word 'duplicate' or similar then maybe consider using the suppress duplicates idea above, export the summary to a new file or report and then use that as the input with a new model (if necessary) which takes each line of the output and checks for a value where the Account Number should be. If the field is blank use a calculated field to display the chosen message.


            A 2 stage process but fairly simple and relatively easy to automate I would think.



            Hope this helps in some way.





            Originally posted by rebecca:

            I want to identify duplicate rows in my report.  Is there a way to say, as in excel,

            =IF(A2=A1, "duplicate"), perhaps using rowno?


            I would like to say if the account number in row 1 = account number in row 2, then "duplicate".  Thoughts? /b[/quote]

            • trying to label duplicates
              Nick Osdale-Popa

              I need to expand this a bit more without resorting to a two-pass solution.


              Grant, your idea of highlighting the key values and coloring them without coloring the supressed duplicates does make those duplicates stand out, however, I was wondering if there was a way to set up the summary to show only those that have duplicated values, sort of filtering the summary if you will.



              • trying to label duplicates
                Data Kruncher



                Let's suppose that you want to filter the summary based on duplicate product names.


                First, add a count field to your measures in the summary definition. Now go back to the summary and double click on the product name column. Go to the Matching tab, and select Measure on the count field where the value is at least 2.


                This will effectively filter out those rows with unique values for product names.


                The only thing to watch out for is that Total Lines in the summary will still be calculating based on the unfiltered data so if you have 100 items, the total count will still show 100 even if only 80 are duplicates. This applies to other summed measure values as well. On that note, I'll pass that along to Gareth as a potential candidate for a correction. I can't think of any situation were you would actually want the totals to not match the listed detail above.




                • trying to label duplicates
                  Nick Osdale-Popa

                  See, I tried that and it doesn't work like I had hoped.


                  We're working on employee earnings for a calendar year.  In the report the employee could be showing up anywhere from 1 to 52 (weeks) on the report.  The Count measure counts the number of times that employee is showing up in the table.  In the summary though, if that employee hasn't changed certain attributes (hire status, union status, etc) they'll appear as one line as all the weeks get totaled together, however their count will show the number of weeks they appear in the report.


                  If the employee has changed a particular attribute, they'll have two (or more) records showing in summary, and their count shows the number of weeks they've had at that status.


                  So what I needs is a count of how many times they're appearing in the Summary window.


                  I can work with coloring (as described above) for now, but I'm hoping to condense it down just those employees that are appearing more than once (we're trying to create an exception report). Obviously I can do this in Excel, I'm just trying to get Monarch to do the bulk of the work for us.

                  • trying to label duplicates
                    Nick Osdale-Popa

                    I've still trying to come up with a solution for this and failing.


                    Anybody have any ideas?

                    • trying to label duplicates
                      Dee Moore



                      Monarch, at least at this point (V8), is not capable of Cellular Referencing.


                      The double pass:


                      Using the Count in the Summary Window, exporting, and building a Calculated Field for the new file in the Table Window:

                      [font="courier"]If(Count>1,"Duplicate","")  /font[/quote]Then Hide the Count Field, is the best choice.


                      But if the number of records being returned is small, you could try this:


                      Create and give focus to an Advanced Filter that returns only the Duplicates.

                      Create a User Defined Field.

                      Copy and Paste "Duplicate" to the cells in the User Defined Field using CtrlC, CtrlV and the Arrow Key.....


                      Choose No Filter when finished.


                      For a small number of records and a single Report pass, this works fine. However, editing the User Defined Field would be necessary for additional Reports.


                      You would be able to automate the Double Pass Models, you would not be able to automate the User Defined Field Model.


                      Dee Moore

                      Datawatch Tech Support