I have an instrument output file in Excel format that contains data for
several hundred chemical compounds. Each compound is listed in column A and
associated data are contained in the following 4 rows across 5 columns; some
cells are null, some contain values, etc. but the output location is
consistent for each chemical. All names and data are formatted as text.

I would like to reorganize the data to contain one row per compound with the
chemical name in column A and associated data in columns B, C, etc. Order
doesn't matter but obviously must be consistent.

How can I do this with either worksheet functions or programming?