Inserting blank rows for missing data in order to transpose

[jmhorne]

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!

 

[Otto Moehrbach]

jm
Is the final result 19 columns with each column having one of the 19
names as the header, and each column having the Original column B values?
In the final product, what would distinguish one row from the next, or is
that not important? Where does the state go?
An easier way to do what you describe is to use VBA. VBA would be able to
determine the beginning and end of any group of colored cells. That would
determine one record. VBA would then be able to place the original column B
value in the appropriate column for that one row (record). VBA would then
move on to the next group of colored cells, and so forth.
Post back if this sounds like what you want. HTH Otto

 

[jmhorne]

jm
Is the final result 19 columns with each column having one of the 19
names as the header, and each column having the Original column B values?
In the final product, what would distinguish one row from the next, or is
that not important? Where does the state go?
An easier way to do what you describe is to use VBA. VBA would be able to
determine the beginning and end of any group of colored cells. That would
determine one record. VBA would then be able to place the original column B
value in the appropriate column for that one row (record). VBA would then
move on to the next group of colored cells, and so forth.

Otto:
Thank you so much for your reply. What you described is exactly
what I would like to do -- I'm basically incompetent when it comes to
VBA and I wasn't even aware that VBA could identify cells based on
their background color formatting. Would you be able to walk me
through how I might be able to accomplish this? Thanks again! --jm

 

[Otto Moehrbach]

jm
Not really. But I can write some code for you and then show you how
that code works. If you wish, send me a file with a small sample of what
you have, say 200 rows or so. Are there just 2 colors involved, or more?
Include in your file a sample of the final product you want including
headers and such. My email address is (e-mail address removed).
Remove the "extra" from this address. HTH Otto