help with re-presenting a table using function or macro

I have a sheet that contains the following information
Sheet 1
Column 1: number: (1-6)
Column 2: text: a colour associated with each number in Column 1 (1=blue, 2=green, 3=yellow, etc)
Column 3: number: the number of different boxes that contain the colours next to the colour

For example, if box 1 contains blue and green, then there will be a number 1 in column 3 in row 1 (blue) and row 2 (green). If box 2 contains green and yellow, there will be a 2 in column 3 in row 2 (green) and row 3 (yellow). There are 5 boxes in total.
See image 1Screen Shot 2017-12-14 at 21.22.37.png

What I would like to do, is then present the same information in a different order on a new sheet
Sheet 2
Column 1: number: the number of each box
Column 2: number: the numbers of the colours in this box
Column 3: text: the names of the colours in this box
See image 2Screen Shot 2017-12-14 at 21.31.07.png

I would like to do this using functions/macros so that if I make any changes to sheet 1, sheet 2 automatically updates.

Any help would be greatly appreciated

Regards

Since there's a direct lookup between a number and a colour why not collect the data in a more useful layout and then apply a lookup formula and present with a Pivot Table.

See attached.

But this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.