7 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    Blanks when pasting into Excel

    mattd390 _

      Hello.  Whenever I paste data from a summary to Excel it enters a blank if the field has a zero in it.  I was wondering if anyone else had this problem when pasting information out of a Monarch summary into Excel.  The field is a numeric field.  I have Monarch Pro V8.0.  Any help would be GREATLY appreciated.  Thankyou!

        • Blanks when pasting into Excel
          Data Kruncher

          Hi mattd390, and welcome to the Monarch forum.

           

          The root cause of the problem is that the record does have any value at all. It's only the view option that allows Monarch to display zeros for null values.

           

          So Excel is pasting the actual value: nothing.

           

          But there is a solution, and it's pretty easy to implement.

           

          What you need to do is create calculated fields to contain zeros if necessary. Let's use a Qty field, extracted from a fictional report, as an example.

           

          Knowing that we'll need a further calculation on the field, I would name the original field in the PreQty. "Pre" is a convention that I use so that fields used in final calcs jump out a bit.

           

          Now in the Table window, created a Numeric calculated field named Qty with this expression:

          If(IsNull(PreQty),0,PreQty)[/CODE]

           

          and hide the PreQty field.

           

          Now when you copy and paste the Qty field into Excel you'll get the zero values you want.

           

          HTH,

          Kruncher

            • Blanks when pasting into Excel
              mattd390 _

              Thank you for the help! I am still getting zeros when I paste into Excel though.  The field that I am using is 1 by default in the table window.  Once I get to the summary window it is being displayed across in a lookup field that has 7 different options.  So there are sometimes zeroes/blanks.  I hope this explains it better for you.  Thanks again for the help!!!

                • Blanks when pasting into Excel
                  Data Kruncher

                  Ah, that clarifies it, but the result you're getting is for the same reasons that I outlined above.

                   

                  When the denominator in a division is zero, Monarch will return a null value for that calculation. That's where your problems are coming from.

                   

                  The solution is to be a little proactive when handling the divisions. Instead of using the typical A/B, use:

                   

                  If(B=0,0,A/B)[/CODE]

                   

                  Use that approach whenever you want to return zeros to Excel instead of blanks in this model.

                   

                  Kruncher

                    • Blanks when pasting into Excel
                      mattd390 _

                      I am sorry, I am not explaining this well.  I am taking transactions from a file and every line gets a calculated field assigned to it as 1.  Each line also has a type of transaction assigned to it through a lookup calculated field.  My summary goes through and adds up all of these 1's and then it displays the lookup field across in the summary to kind of give it it's titles and the qty field total is displayed for each type in the lookup.  So I have an account name, a persons name then 1 2 3 and 4 across the top of my summary and a total.  If any of the 1 through 4 are empty, it pastes a zero in Excel.  Sorry for my lack of being able to explain it better, I have only been using this program for a little while.  Once again, I appreciate greatly all of your previous help and any other help you could give!

                        • Blanks when pasting into Excel
                          Data Kruncher

                          Of course it didn't help that I was rushing along and overlooked the whole "when using a summary" aspect of the problem. So my If() thing is interesting (maybe) but not entirely useful or applicable here. :o

                           

                          Will reconsider and revisit as time allows.

                            • Blanks when pasting into Excel
                              mattd390 _

                              Of course it didn't help that I was rushing along and overlooked the whole "when using a summary" aspect of the problem. So my If() thing is interesting (maybe) but not entirely useful or applicable here. :o

                               

                              Will reconsider and revisit as time allows.[/QUOTE]

                               

                              I definitely already took note of all of your other suggestions and added them to my Monarch notebook :).  I appreciate you spending time on this!  If I could I up upload my model and my text file but I work in a field that demands confidentiality.

                                • Blanks when pasting into Excel
                                  Grant Perkins

                                  Matt,

                                   

                                  Kruncher has better Excel experience than I but from memory the issue with summaries is that the 0 is not really a zero numerically, it is a display feature that caan be set to avoid nulls and stuff appearing. Cut and past takes the actual content of the cell, not the substitue display.

                                   

                                  The regular solution is either to export the summary (which will keep the zeros) to another 'report' file and then run that through another model (or maybe just cut and paste it? Not sure on that at the excel end) so that the zeros become 'real'.

                                   

                                  The basis of the question is whther you are dealing with real zero values or a calculation which fails for reasons of data mismatch and then displays as zero. Can you fix the data in the table so that calculation ability in the table presents valid numeric outputs?

                                   

                                  Beyond those comments I will leave it to Kruncher to come up with some ideas.

                                   

                                  HTH in some way.

                                   

                                   

                                  Grant