Hi Will,

You can insert a helper column next to your data and put 1,2,3 and 4
in the first four cells, then highlight all four cells, grab the fill handle
and drag it down to the end of your data while holding down the ctrl key.
This will leave you with repeating series of 1 to 4 running down the column.

You can now use autofilter on your helper column. Filtering on 1 should
give you all the compound A's, filtering on 2 should give you all the B's
etc.

Then just copy and paste each filter to a new worksheet.

HTH
Martin