How to Update/Merge rows when another row is created with the same name?

Options

I am creating a design matrix of sorts to track employee skill levels and allow for employees to reach out to others with higher skill level for mentorship.

It is intended that this sheet would be updated whenever an employee felt that there skill level had been enhanced. Employees have access to a form but not the sheet so whenever they update their skills the sheet does not auto-update and merge the rows. I am wondering if there is a way to do this, if Smartsheet can detect that there is duplicates of the same name and then update the old row with the new values, whilst keeping the old values which are not updated.

For example Aaron has updated his skills twice since the original (blue) I would like the new values to be moved into the original overriding the old values, but only for the skills that have new values, and ignore the blanks.

Any help on this is greatly appreciated.

Best Answer

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Dean

    We had a similar situation. The workaround that we followed was creating a separate sheet for the team members to record their skills. A separate tracker sheet contains names of all the employees and and the sheet uses INDEX/MATCH to get the latest value/last entry from the input sheet.

    Here is how it goes

    On sheet 1, create an auto-number column. On sheet 2, create a column "LastRecord", which will read the highest number value from the auto-number column you created in sheet 1 using below formula

    =MAX({auto number column of first sheet})

    You can use a series of INDEX/MATCH formulae to gather your last row values:

    =INDEX({Employee}, MATCH(LastRow@row, {auto number column on first sheet}, 0))

    You can replicate this for all the skills

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

Answers

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Dean

    We had a similar situation. The workaround that we followed was creating a separate sheet for the team members to record their skills. A separate tracker sheet contains names of all the employees and and the sheet uses INDEX/MATCH to get the latest value/last entry from the input sheet.

    Here is how it goes

    On sheet 1, create an auto-number column. On sheet 2, create a column "LastRecord", which will read the highest number value from the auto-number column you created in sheet 1 using below formula

    =MAX({auto number column of first sheet})

    You can use a series of INDEX/MATCH formulae to gather your last row values:

    =INDEX({Employee}, MATCH(LastRow@row, {auto number column on first sheet}, 0))

    You can replicate this for all the skills

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!