Assume your data is arranged like this:
Co1 red 200 ppr
Co1 blu 50 pen
Co2 pur 300 mrk
Co2 org 60 jot
Co3 yel 250 nte
Co3 grn 502 ers
comp


Co1 red 200 ppr blu 50 pen
Co2 pur 300 mrk org 60 jot
Co3 yel 250 nte grn 502 ers

Select B1:D6 and name it array1
Use Insert > Name > Define
Verify by selecting those 18 cells and checking that
the name window says array1.
Select A1:A7 and
Insert > Name > Create > Bottom Row
Select Co1 thru Co3 and verify name.
At A10:A12, enter this formula:
=INDEX(comp,ROW()*2-19)
At B10:G12, enter this formula:
=INDEX(array1,FLOOR((COLUMN()-2)/3,1)+ROW()*2-19,MOD(COLUMN()-2,3)+1)
If you want to move the results to a different row,
you have to change the number 19 in the formulas.