I'm not sure what this is called, but I want to reformat excel data from this;
to this;
In other words, loop though the column headers and add each of the data points in a new grouping. And display the results on a new tab.
I'm not sure what this is called, but I want to reformat excel data from this;
to this;
In other words, loop though the column headers and add each of the data points in a new grouping. And display the results on a new tab.
This solution is going to be a bit on the generic side. Lets assume your source table is in B2:D5. This way you can adapt it to a table located anywhere on your sheet. Where as a table starting in A1 may wind up using some short cuts.
Place the following two formulas in G3 and H3 respectively:
=IFERROR(INDEX($B$2:$D$2,MOD(ROWS($G$3:G3),COUNTA($B$2:$D$2)+1)),"")
=IFERROR(INDEX($B$3:$D$5,ROUNDUP((ROWS($H$3:H3))/(COUNTA($B$2:$D$2)+1),0),MOD(ROWS($H$3:H3),COUNTA($B$2:$D$2)+1)),"")
The first formula uses the mod function to generate a counter. As the formula is copied down, $G$3:G3 changes to $G$3:G4and the ROWS function returns 1 for the first row 2 for the second row and so on. Adjust $G$3:G3 to be the cell of where you want your table to start. The countA function is there to determine how many columns you have. The $B$2:$D$2 range will need to be adjusted to suit your column headers. You cannot have any blank headers. The +1 is required to augment the column count by 1 in order to create the blank line between groups.
The mod function gets nested in the INDEX function $B$2:$D$2. It will count through a series of numbers representing the column to look in, flip to zero and repeat. When mod returns a value of 0, INDEX cant deal with it and generates an error. By nesting that in an IFERROR function the error value can be suppressed and and "" is returned instead. Visually it looks like a blank, but there is still a formula in the cell.
The second function is basically the same as the first, except instead of pointed at the header row in a 1D range, the remainder of the table is used in INDEX for a 2D range of $B$3:$D$5. Since its a 2D range, INDEX need to be told what row to look and and what column to look in. Since the first formula has a portion that already takes care of what column to look in that can just be copied over. In order to determine what row to look in, this portion of the second formula does that:
ROUNDUP((ROWS($H$3:H3))/(COUNTA($B$2:$D$2)+1),0)
Again change the $H$3:H3 to the cell for the start of your second column, and the $B$2:$D$2 to match your column header row.
Now when selecting your ranges that are located on another worksheet the range will start with the the worksheet name followed by an ' followed by your range. Excel will automatically do this for you as you enter a formula and you navigate to another sheet.
The following is an example of a range on worksheet named Sheet1
Sheet1!B3:D6
Notice is has no space in the name. If the worksheet had a space in the name like say worksheet named RED BLUE then the reference would look like:
'RED BLUE'!B3:D6