Hello,

I have a spreadsheet containing three columns:

Column A includes variable names
Column B includes values corresponding to those variables
Column C contains the state abbreviation corresponding to that record

There are thousands of records on this sheet, with each record
occupying up to 19 rows of different variable names and their
corresponding values.  There are a total of 19 possible variable
names, but not every record uses all of them; however, those that do
appear for any record will always appear in alphabetical order.

New records are indicated by alternating background colors.  For
example, Record 1 may occupy 12 rows with white background color,
Record 2 will occupy the next 15 rows with gray background, and Record
3 will occupy the next 19 rows with white background again, looking
something like this (imagining a new record beginning where I have
placed a row of periods):

================================================
A (variable name)             |     B  (values)          |     C
(state)
================================================
coop_agree_percent                50                           AR
degree_content                       Management            AR
degree_content_area               Public Health           AR
educ_qual                              Masters                   AR
expertise_areas                      CVD                        AR
oth_state_percent                   75                           AR
other_percent                         30                           AR
percent_time                          100                          AR
position                                  Data Manager          AR
position_source_funds             State                      AR
staff_type                               Permanent              AR
state_percent                         66                           AR
...............................................................................................
coop_agree_percent                40                           AR
degree_content                       Epi                         AR
degree_content_area               Public Health           AR
educ_qual                              Masters                   AR
expertise_areas                      Other                      AR
expertise_areas_other             Surv                        AR
foundation_percent                  10                           AR
oth_state_percent                   60                           AR
other_percent                         45                           AR
percent_time                          15                           AR
position                                  Data Manager          AR
position_source_funds             Grant                      AR
staff_name                             John Doe                 AR
staff_type                               Interim
AR
state_percent                         33                           AR
...............................................................................................
coop_agree_percent                25                           AZ
degree_content                       PubPol                    AZ
degree_content_area               IntlHealth                 AZ
educ_qual                              PhD                         AZ
expertise_areas                      Other                      AZ
expertise_areas_other             Dependence            AZ
foundation_percent                  30                           AZ
oth_state_percent                   50                           AZ
other_percent                         15                           AZ
percent_time                          25                           AZ
position                                  Other
AZ
position_other                         Fellow                     AZ
position_source_funds             State, Other            AZ
position_source_funds_state    50                          AZ
position_source_funds_other    50                           AZ
project_start_date                   09/01/2004              AZ
staff_name                             John Doe                 AZ
staff_type                               Permanent               AZ
state_percent                         20                           AZ
=================================================

Ultimately, I would like to be able to transpose this data so that
each record occurs on one row, with the many values appearing in
columns that each represent one of the 19 possible variable names.  I
do have a method for transposing the data *if the number of rows per
record is the same*.

My question, therefore, is: how can I automatically insert blank rows
into each record wherever one of the 19 variables is missing?  The
blank row would need to be inserted in the correct alphabetical order
(in other words, it could not be added at the end).

Is there a way to quickly achieve this goal?  Is there a better way of
transposing the data that I do have?  Like I said earlier, there are
thousands of records occupying nearly 30,000 rows.  I wish I could
just do this by hand, but it would take me days to do so.

Thank you so much in advance for any assistance or suggestions that
anyone can offer.  Any help will be greatly appreciated!