Maybe this might help.

Say your data is in column B starting at B4. In a blank column off to the
right, say M, put this formula and copy down as far as needed.

=OFFSET(B$4,(ROWS($1:1)-1)*4,,)
that should extract all your Compound A's

use
=OFFSET(B$5,(ROWS($1:1)-1)*4,,)
in column N for compound B's
etc.
etc.

HTH
Martin