Is there a way to pull cells that meet criteria into individual cells in another sheet?

Ann.Cracovaner

I'm working on a multi-sheet solution for my PMO and have been using Index and Match to pull specific information from a metadata sheet into matching cells on other sheet using INDEX/MATCH. I've been using either the Change Order (CO)number or the Scope Change ID (Larger Project Identifier) for most of the solution, but I've run into an issue where I need all of the CO numbers associated with that Scope Change ID to copy over into a new sheet.

I know that you could do this in a report, but there are some additional new fields that need to be on the sheet so I don't think generating a report is an option. Is there a variation on INDEX/COLLECT that would all each cell in the CO number column to be a unique value that matches the Scope Change ID?

Source Sheet:

Destination Sheet:

Jason Tarpinian

JOIN(COLLECT()) will return all of the values into a single cell, but is kind of useless if you intend to pull individual CO data from the other sheet as well. Using CHAR(10) as a line break and wrapping the text is my favorite way to JOIN values.

=JOIN(COLLECT([CO Number]:[CO Number], [Scope Change ID]:[Scope Change ID], [Scope Change ID (CO Number Match)]@row), CHAR(10))

If they need to be on separate lines is a bit more difficult, because you would have to know how many (possible) CO's there could be for each Scope Change ID. So you could build out placeholders for each Scope ID like below. And if that particular Scope ID doesn't have the nth Change Order, it returns an error (which you could clean up with IFERROR if you'd like)

=INDEX(COLLECT([CO Number]:[CO Number], [Scope Change ID]:[Scope Change ID], [Scope Change ID (CO Number Match)]@row), [CO Match #]@row)