How to retrieve multiple values from a column based on a primary key from a different column


I have data in Sheet 1 like mentioned below -

I need to get values in a different sheet ( Sheet 2) like mentioned below -

Basically I would like to display all the jira story IDs ( unique values) and list all the associated engagement IDs (multiple matching values).

Please help me with a solution for this.


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Balaji chandra

    Insert a text/number column on sheet 2 (called "Number" in this example) and enter the numbers 1 through whatever you think you will need. If you expect 50 unique Jira Story IDs then put in 1 - 50 in the Number column (maybe go to 75 to give yourself a "just in case" buffer).

    Then to pull in the Jira Story IDs you would use:

    =IFERROR(INDEX(DISTINCT(COLLECT({Jira Story}, {Jira Story}, @cell <> "")), Number@row), "")

    Then you would use this to bring in each list of Engagement IDs:

    =JOIN(COLLECT({Engagement ID}, {Jira Story}, @cell = [Jira Story]@row), ", ")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!