My excel add-on “Data Separation” has two major functions called RowToSheet and ColumnToSheet. The former generates new sheets for each row together with the header row. For example, imagine that the table below is the master sheet.
Sheet 0. Master Sheet
| Month | All Paper | Cardboard | Metal | Glass | Plastic/Cans |
| Jul, 2014 | 13294 | 6307 | 7882 | 0 | 577.5 |
| Aug, 2014 | 7120 | 12444 | 6080 | 500 | 1000 |
| Sep, 2014 | 8655 | 10923 | 5358 | 500 | 1519 |
| Oct, 2014 | 10848 | 6748 | 812 | 670 | 1543 |
So, if we choose the menu “RowToColumn”, each row starting from “July, 2014” will be replicated to new sheets together with the header row, and the names of the sheet are assigned the month and the year. Technically, the cell in the first column in the chosen row becomes the name of the sheet for this function. In this example, four new sheets will be generated.
Sheet 1. “July, 14”
| Month | All Paper | Cardboard | Metal | Glass | Plastic/Cans |
| Jul, 14 | 13294 | 6307 | 7882 | 0 | 577.5 |
Sheet 2. “Aug, 14”
| Month | All Paper | Cardboard | Metal | Glass | Plastic/Cans |
| Aug, 14 | 7120 | 12444 | 6080 | 500 | 1000 |
Sheet 3. “Sep, 14”
| Month | All Paper | Cardboard | Metal | Glass | Plastic/Cans |
| Sep, 14 | 8655 | 10923 | 5358 | 500 | 1519 |
Sheet 4. “Oct, 14”
| Month | All Paper | Cardboard | Metal | Glass | Plastic/Cans |
| Oct, 14 | 10848 | 6748 | 812 | 670 | 1543 |
If the sheet already exists, the data will be cleared within that child sheet, and related data from master sheet will be imported. Only the master sheet needs to be modified. One inconvenient aspect of the initial design involved clicking the button “RowToSheet” in order for the changes to take effect in children sheets.
“ColumnToSheet” works similarly. The only difference is that it replicates the data from master sheet column by column to child sheets. For example, using the same master sheet as in Sheet 0, five child sheets will be generated because there are six columns, and the header column doesn’t need to have a separate sheet of its own. Instead it is copied to every child sheet. The title of the sheet is assigned the column header of the replicated column, i.e., technically, the name of the sheet is assigned the value from the cell in the first row in the chosen column.
From the example Sheet 0, these are the output sheets by applying “ColumnToSheet” in DataSeparation.
Sheet 1. “All Paper”
| Month | All Paper |
| Jul, 14 | 13294 |
| Aug, 14 | 7120 |
| Sep, 14 | 8655 |
| Oct, 14 | 10848 |
Sheet 2. “Cardboard”
| Month | Cardboard |
| Jul, 14 | 6307 |
| Aug, 14 | 12444 |
| Sep, 14 | 10923 |
| Oct, 14 | 6748 |
Sheet 3. “Metal”
| Month | Metal |
| Jul, 14 | 7882 |
| Aug, 14 | 6080 |
| Sep, 14 | 5358 |
| Oct, 14 | 812 |
Sheet 4. “Glass”
| Month | Glass |
| Jul, 14 | 0 |
| Aug, 14 | 500 |
| Sep, 14 | 500 |
| Oct, 14 | 670 |
Sheet 5. “Plastic/Cans”
| Month | Plastic/Cans |
| Jul, 14 | 577.5 |
| Aug, 14 | 1000 |
| Sep, 14 | 1519 |
| Oct, 14 | 1543 |
After several modifications, the add-on has been improved further so that the parent sheet is directly connected to children sheets, thus the button doesn’t need to be clicked every time the data requires update. All the data is now automatically updated simply by modifying the parent sheet.
Computer Science Internship 