combine nmultiple rows of data to single

I have a spread sheet that has this format:
product supplier address color
1 supplier address blue
2 supplier address red
1 supplier address red
2 supplier address purple


I need to get the sheet to show me:
product supplier address color color
1 supplier address blue red
2 supplier address blue purple

After searching with no luck pivot tables will not do this, of course. I have about 6000 rows of data to combine this way.

hope this helps out.

That is about what I was getting the only issue is the Sub1 Sub2 and so on have to be labeled with that. I thought a script or another worksheet would make it pull the data into a format that is needed.