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

Balaji chandra

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

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

Balaji chandra

@Paul Newcome could you please help ?

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

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. 👍️