2 Replies Latest reply: May 15, 2014 9:58 AM by barcand _ RSS

    Compare 2 Excel sheets and update 4 columns from 1 when a match is found

    barcand _

      How do I take 2 Excel spreadsheets and do a compare(lookup) and update the data on 1 sheet to another creating a final listing of updated data.

        • Compare 2 Excel sheets and update 4 columns from 1 when a match is found
          joey

          As I understand your question, you have two spreadsheets, A and B.  There are several columns on each, but the both contain columns k and v. Column k is the unique key of the rows in each spreadsheet that you use to lookup.  v is the values that you are looking to correct.  You want to create spreadsheet C, which has the data from A, but the columns v have been updated to the values from spreadsheet B.  Correct me if I'm wrong in my understanding.

           

          If this is the case, you need a model that reads A as a database.  It will have an external lookup to spreadsheet B based on columns k as the key, and returning columns v.

           

          You will then have the following table in Monarch:

          k v  B.v

           

          You need to add a calculated field for each column you want to correct:

           

          v-Output = if(isnull(B.v),v,B.v)

           

          Then hide all of the v and B.V columns.

           

          I appologize if the column and table names are confusing, but this is the basic concept you will need to follow.  Let me know if you have any specific questions on this.

          • Compare 2 Excel sheets and update 4 columns from 1 when a match is found
            barcand _

            Thank you sir I will try this out. Much appreciated