| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting blank rows for missing data in order to transpose
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inserting blank rows for missing data in order to transpose
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 wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inserting blank rows for missing data in order to transpose
On May 2, 4:12 pm, "Otto Moehrbach"
wrote: 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 wrote in message ... 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: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Inserting blank rows for missing data in order to transpose
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Help- Inserting Blank Rows | Excel Discussion (Misc queries) | |||
| Inserting blank rows in Excel. | Excel Discussion (Misc queries) | |||
| Inserting Blank rows after every row upto 2500 rows | Excel Worksheet Functions | |||
| Inserting blank entries for missing values | Excel Worksheet Functions | |||
| Is there a way to transpose and invert the order of data in Excel. | Excel Worksheet Functions | |||
Linear Mode
