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.

Thanks

Find more posts tagged with

Accepted answers

Paul Newcome

@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), ", ")

All comments

Balaji chandra

@Paul Newcome could you please help ?

Paul Newcome

@Balaji chandra

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), ", ")

Balaji chandra

@Paul Newcome Thank you so much. It worked.

If you don't mind , could you please explain how this formula works. Meaning how each of these functions mentioned works to get us the result.

Paul Newcome

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

COLLECT pulls all of the values form the {Jira Story} range that are not blank into a list.

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

DISTINCT shortens that list to only have one entry of each within the list.

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

INDEX will pull from that list the 1st, 2nd, 3rd, etc. entry based on the Number@row.

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

COLLECT pulls together a list of all cells within the {Engagement ID} range that have the same value as [Jira Story]@row in the {Jira Story} range.

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

JOIN outputs that list with a "comma space" delimiter.

Balaji chandra

@Paul Newcome thank you again. This helps a lot.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.