changing excel data formatting from vertical to horizontal [duplicate]
This question already has an answer here:
How to “unpivot” or “reverse pivot” in Excel?
11 answers
How do I transpose columns in Excel?
1 answer
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.
microsoft-excel
asked Jan 22 at 22:43
jpolachejpolache
433
marked as duplicate by Máté Juhász, Excellll, PeterH, BillP3rd, LotPings Jan 25 at 14:26
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
How to “unpivot” or “reverse pivot” in Excel?
11 answers
How do I transpose columns in Excel?
1 answer
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.
microsoft-excel
asked Jan 22 at 22:43
jpolachejpolache
433
marked as duplicate by Máté Juhász, Excellll, PeterH, BillP3rd, LotPings Jan 25 at 14:26
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
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 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 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 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 at 4:00
add a comment |
This question already has an answer here:
How to “unpivot” or “reverse pivot” in Excel?
11 answers
How do I transpose columns in Excel?
1 answer
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.
microsoft-excel
asked Jan 22 at 22:43
jpolachejpolache
433
This question already has an answer here:
How to “unpivot” or “reverse pivot” in Excel?
11 answers
How do I transpose columns in Excel?
1 answer
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 question already has an answer here:
How to “unpivot” or “reverse pivot” in Excel?
11 answers
How do I transpose columns in Excel?
1 answer
microsoft-excel
microsoft-excel
asked Jan 22 at 22:43
jpolachejpolache
433
asked Jan 22 at 22:43
jpolachejpolache
433
asked Jan 22 at 22:43
jpolachejpolache
433
asked Jan 22 at 22:43
jpolachejpolache
433
asked Jan 22 at 22:43
jpolachejpolache
433
433
marked as duplicate by Máté Juhász, Excellll, PeterH, BillP3rd, LotPings Jan 25 at 14:26
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Máté Juhász, Excellll, PeterH, BillP3rd, LotPings Jan 25 at 14:26
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
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 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 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 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 at 4:00
add a comment |
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 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 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 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 at 4:00
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 at 23:39
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 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 at 0:27
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 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 at 3:55
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 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 at 4:00
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 at 4:00
add a comment |
1 Answer
1
active
oldest
votes
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
answered Jan 23 at 6:11
Forward EdForward Ed
856214
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 23 at 6:11
Forward EdForward Ed
856214
add a comment |
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
answered Jan 23 at 6:11
Forward EdForward Ed
856214
add a comment |
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
answered Jan 23 at 6:11
Forward EdForward Ed
856214
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
answered Jan 23 at 6:11
Forward EdForward Ed
856214
answered Jan 23 at 6:11
Forward EdForward Ed
856214
answered Jan 23 at 6:11
Forward EdForward Ed
856214
answered Jan 23 at 6:11
Forward EdForward Ed
856214
856214
add a comment |
add a comment |
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 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 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 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 at 4:00