I have a multi-step problem I am trying to solve with either Google Sheets or Excel. First, I have a sheet which lists all the names and the assigned classes and I need to find occurrences of the Class name and put each of the names into a single cell next to the Class name. Then, on another sheet I have each of the names linked to one of four colours. I then need to pull the matching colour from this sheet and apply it to each instance of the name in the cell.
|This kid||Class A||Class C||Class B|
|That kid||Class C||Class D||Class H|
|Your kid||Class B||Class A||Class E|
|Other kid||Class E||Class F||Class A|
Worksheet 3 (Final product)
|Class A||This kid; That kid; Other kid|
|Class B||Other kid; Your kid; That kid; This kid|
I have been struggling with this for a couple of years and would appreciate any help I could get.