1

I'm not sure what this is called, but I want to reformat excel data from this;

format 1

to this;

enter image description here

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.

4
  • It's called transposing. Do you want to do this one time only? Or does it have to be repeatable? Do you want to use VBA or avoid it? What have you tried? Why doesn't what you tried work for you? Update your question, then post a comment.
    – teylyn
    Jan 22, 2019 at 23:39
  • Transposing will turn the original table on its side. If you need each slice in a separate range, that will take a different approach or second step.
    – fixer1234
    Jan 23, 2019 at 0:27
  • fixer1234 is correct in that I need a slice for each row of data. From what I can tell transposing only moves column headers to "row headers". I need to change my rows of data into "lists" as in my example.
    – jpolache
    Jan 23, 2019 at 3:55
  • teylyn, see my other comment re:transposing. Am I missing something? I want to have this built into a template I am using at work, so it does need to be repeatable. I would prefer to avoid VBA if possible. I have not tried anything yet because I could not think of what to try and did not find any examples in my web searches. If what I want is not transposing, is there another name for it?
    – jpolache
    Jan 23, 2019 at 4:00

1 Answer 1

1

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

POC

Not the answer you're looking for? Browse other questions tagged .