Need help with lookup and replace values

Vince Leedy
Vince Leedy New Altair Community Member
edited August 2022 in Community Q&A

Would someone please help me with how to do a lookup and replace data?

I have two spreadsheets - main and approvers.

The main spreadsheet contains:

  • A list of employees in column 1
  • 10 columns of fruit (in each column an employee has one of four positions or is blank)
  • The last column is labelled "Approver" but has no data in it

The approvers spreadsheet contains:

  • A list of the fruit in column 1
  • The approver for that fruit in column 2
  • The last column is labelled "Position", but has no data in it

I need to do several things with this data:

  • For each fruit on the approvers spreadsheet, find the entry for the approver on the main spreadsheet.  Move (cut and paste) that approver's position from it's location on the main spreadsheet to the Position column on the approver spreadsheet.  If the employee does not have a position for that fruit, then the Position column should be marked "No Position".
    • Example 1: Upp, Harriet is the approver for Apple (approver spreadsheet, row 2).  On the main spreadsheet, look up Harriet's position for Apple - she is a Buyer.  Cut and paste Buyer from main spreadsheet cell B19 to approver spreadsheet cell C2.
    • Example 2: Yose, Audie is the approver for Banana (approver spreadsheet, row 5).  On the main spreadsheet, look up Audie's position for Banana - he has no position.  On the approver spreadsheet, in the Position column for Banana (cell C5), enter the text "No Position".
  • Once all of the approvers have been taken care of on the Position column on the approver spreadsheet, then fill in the data for the Approver column on the main spreadsheet.  For employee who is on the approver spreadsheet, then the Approver column on the main spreadsheet should be marked "Approver".
    • Example 1: Upp, Harriet is on the approvers spreadsheet (cells B2 and B3).  On the main spreadsheet, in cell L19, enter the text "Approver".
    • Example 2: Anthemum, Chris is on the approvers spreadsheet (cells B4 and B6).  On the main spreadsheet, in cell L16, enter the text "Approver".

I have attached before and after examples of both spreadsheets.  Any help would be greatly appreciated!

Tagged:

Answers

  • Clinton Chee_22243
    Clinton Chee_22243 New Altair Community Member
    edited January 4

    This is a very nice challenge that highlights the power of Monarch over the manual work in Excel.

    I've completed the solution and attach the "FruitApprover.dpwx" but I had to ZIP the attached file here.

    When you open the dpwx, you will see the Yellow Warning marks - simply because you need to tell the Monarch where is your copy of the data files again - by rightclick on the yellow items, select 'Edit file paths'.

    Part 2 of the solution you described, actually is the part I did first.

    I imported the 4 spreadsheets you provided, and in Monarch, they are named as: main, approver, main-complete, approver-complete (the last two is just used as a reference).

     

    Part 2 - Filling in the Main sheet, at the "Approver" column, with the words "Approver" or blank.

    i) starting with approver -> GROUP by Approver column to have a unique list of approvers -> get only 5 unique names -> call this table "GroupUniqueApprover"

    ii) Left Join -> main table and GroupUniqueApprover. This will retain the structure of the main table, and join the 'Approver' column at the very end. Three more steps of column calculations, and this transform the Approver column (which contain names) into a column with the words "Approver". Result table is called "MainComplete" - can be checked with "main-complete" table.

    Part 1 - Filling in the original Approver table

    i) Using "Main Complete" table, extract rows, based on the Approver column. This cuts down the main-looking table with 30 employees, down to 5 employees which are approvers. Resultant table called "Extract Rows" has one Employee column and 10 Fruits column and Approver column

    ii) Unpivot the "Extract Rows" table - result in the table called "Unpivot". Now the 10 fruit columns all become two vertical columns which are Fruit and Approver.

    iii) Left Join the "approver" table with the "Unpivot" table, to retain all rows of the original approver table. The Position will be correctly placed simply due to the left join operation. For rows that are blank, use the column Replace Nulls function to give it the text "No Position". This final table is called "join" and can be compared with "approver-complete"

    Hope that helps...... if you have further questions please let me know at chee@altair.com

     

     

     

  • CPorthouse
    CPorthouse
    Altair Employee
    edited August 2022

    I also did something similar to Clinton's response but using different logic.  There is almost always more than one way to achieve a result through Monarch.

    I started by unpivoting the main spreadsheet to get a list of employees and their role for each fruit.  I then used a lookup join on the approvers worksheet to my unpivot table matching on the fruit and approver/employee name.  I could then simply replace the null/missing values with "No Position", and simply clean up the columns a little.

    The second step, I also used a lookup join on the original main and approvers worksheets.  Lookup joins only return the first match so I didn't require a unique list.  Using formula to replace non-null values with "Approver" and some column clean up, I ended up with a similar result.  I have attached my workspace as well (same issue as Clinton...you will need to unzip and replace file names to match your environment).

Categories