Is it possible to convert data found in Sheet #1 to the format found in Sheet #2

Sheet1 - Current dataset.

Sheet2 - Expected Outcome using formulas.

• Column B contains a unique class ID for each row.

  • A class ID may have 4-20 characters, it may look like: "Class001", "CID001", "V001", or something else (because whoever created this data could not decide on a unified format lol)

  • Currently I am using this formula. Is there a better formula? =UNIQUE(Sheet1!I2:I) placed in Sheet2!B2.

• Column C contains all the employee ID's in a single row (separated by a space) who participated in that particular class.

  • Some employees have participated in multiple classes (example Employee 222222 was in Class002 and Class009) so the same Employee ID can be found in multiple rows.
  • I tried using =ARRAYFORMULA(VLOOKUP(Sheet2!B2:B,Sheet1!{I2:I,B2:B},2,0)) in Sheet2!C2 but this will only return the first match. Perhaps there is a way to use vlookup to return an array of all Employee ID's matching the Class ID#, transpose them, then concat them? What do you suggest?

Thanks for your help! :)

CodePudding user response：

Try:

=JOIN(" ",FILTER(Sheet1!B:B,Sheet1!I:I=B2))

Then drag it down for the other Classes