7 Replies Latest reply: May 15, 2014 9:53 AM by Scott Johnson RSS

    Differences

    Johnny _

      I am new to Monarch completely, so please be patient with me.  Here is what I am needing to accomplish.  I have two excel spreadsheets they are both a listing of part numbers and quantities.  What I am needing is for monarch to combine the two lists and pull out whatever part numbers are not on both, as well as to show me only the parts with different quantities?  For example if part number 1 has a qty of 5 on excel spreadsheet a and excel spreadsheet b has the same part with a qty of 2 I need monarch to list this part with a qty of 3?  Any suggestions?  Any and all help is greatly appreciated.

       

      Thanks,

       

      John  :confused:

        • Differences
          Grant Perkins

          John,

           

          Which version of Monarch do you have and is it Standard or Pro? ( I assume Pro as you are asking this question ...)

           

           

          Grant

          • Differences
            Johnny _

            I have Monarch Pro 7.02

             

            Thanks for your help in this matter.

            • Differences
              Grant Perkins

              John,

               

              Nowhere can I find the solution I was thinking of. I'm sure I have seen the ideal process reported either in the forum or the e-newsletter but I have failed to find it so far.

               

              So, here is something I will call a 'wheel' ... you can enhance it and maybe combine steps for convenience.

               

              First you need to get a consolidated list of the Part Numbers from each Excel spread sheet. If the number of codes is not too big and the process does not require automation you can simply cut and paste the cells into a combined worksheet. Sorting that is optional. Of course you could also use Monarch to extract just the codes from each worksheet separately and then EXPORT the results to the same Export file using an APPEND method for the second part of those exports.

               

              From a Monarch session load the new worksheet/file as a database. Create a summary by Part Number as the key field and using count as a measure.

               

              I would expect this to give a list of all the part numbers with either a 2 count (appears in both lists) or a 1 count (appears in only one list). If you get a 3 count you may want to check the original worksheets.

               

              Export this to a file of your choice (Report, CSV or similar or Excel if the number of rows will fit) ready for the next stage.

               

              Open the newly exported file which should now have 2 columns - code and count. Create an external link to the first of the original worksheets and bring in the quantity field. Repeat that to create another external link to the other worksheet bringing in that qty field. (Differentiate between them using suitable names).

               

              So you should now have a 4 column table with Part Number, COUNT and 2 quantities. If COUNT = 1 then the code only appears in one input.

               

              Create a calculated field to calculated the Qty difference between the two quantity fields.

               

              Where the difference is numeric but non zero you know the Codes are in both sheets but the quantities are different.

               

               

              Alternative process.

               

              You could possibly read in the quantities from the other sheets at the point of reading in the new 'master' list and skip the summary export stage. This would, where the Code appears on BOTH worksheets, record the quantities from both lists against each code found.

               

              So if you them do a SUM of the quantities in the summary you will get the zero result if both quantities are the same but a wrong result for the actual difference where the code appears in both worksheets.

               

              There are ways around this if the actual difference in quantity needs to be reported. For example you could use the MAX(Qty1)-MAX(Qty2) concept which will use only the highest value (in effect in this case the value you want) and should give the correct answer.

               

              ... unless you find there are more the 2 entries for a code of course!

               

              I hope this helps and means something to you at this stage of your Monarch introduction.

               

              Feel free to come back with further questions - this is easier to do than to describe - and I will do my best to answer them.

               

              And of course keep an eye open for better solutions from other posters!

               

              Most importantly - have fun.

               

               

              Grant

              • Differences
                RalphB _

                John,

                 

                I do something similar to what you want with some of the files I work with.

                 

                First, I would open one of your spreadsheets as a database in Monarch.  I would then add a calculated field to denote which file it is coming from.  Say you create a calculated field called source and have it put an "A" for the first file.  Then export the table out to a database or another excel spreadsheet, depending on the size of both sheets.

                 

                Next, I would do the same to the second spreadsheet but have it put a "B" in the source column and APPEND it to the same database you did with the first file.

                 

                Next, I would open the new database/spreadsheet in Monarch.  In it, I would add a couple of calculated field to split out the quantities into separate columns for the 2 files.

                 

                In one column, you could name it file1 and add the formula "If(source = "A",quantity,0)".  Do the same thing for the second file in another calculated field.

                 

                You should then end up with a column "file1" with quantities from the first file and another column "file2" with the quantities from the second file.

                 

                I would then add a third calculated field called "net" computing the difference between "file1" quantity and "file2" quantity.

                 

                Next, add a summary sheet with key value of the parts number and the file1, file2 and net columns in the measure field.

                 

                If the quantity was the same for a part number in both sheet A and sheet B, the net column should be 0. Otherwise you should see the net difference between the 2 sheets.

                 

                If you want to see only what has changed between the 2 sheets, export this summary out and open it back up in Monarch and filter by the net column.

                 

                Hope this helps to get you rolling.

                 

                Ralph

                • Differences
                  Johnny _

                  Thank you all so much, unfortunately, I had to go through and do all of this manually, so needless to say NEXT time I will definately be using all of your usefull tips.  Again, I really appreciate the time everyone took to assist a newbie such as myself.

                   

                  Thanks A Million!

                   

                  John

                  • Differences
                    Arne _

                    John,

                    Sorry, don't have a MONARCH solution, but why don't you use VLOOKUP function in excel? Entering the function in file b will give you quatity for same part in file a and then you can add...

                    Regards, Arne

                    • Differences
                      Scott Johnson

                      I think I remember something similar to this a while back.  If I remember correctly a simple solution was to create a calculated field that combined the part number and the quantity.  Add another field that has the number 1 for the first list and 2 for the second list.  Go to summary and you have 1's for first list, 2's for second list and 3's for matches.