Hello,
I have a long string of data that is laid out horizontally and I am trying to find a way (without VBA) to relatively easily convert it to be vertically stacked. Below is a small example of what I need to be able to do on a much larger scale.
If I set up the horizontal spreadsheet to have the 6 rows for each 1 row of data on the horizontal, is there a formula that allows me to look up a combination of styles and types from the vertical table and grab them from both the column and row in the horizontal table?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 519"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]Category[/TD]
[TD]Style[/TD]
[TD]Jan Plan[/TD]
[TD]Jan Act[/TD]
[TD]Jan Var[/TD]
[TD]Feb Plan[/TD]
[TD]Feb Act[/TD]
[TD]Feb Var[/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Department[/TD]
[TD]Category[/TD]
[TD]Style[/TD]
[TD]Type[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Jan Plan[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Jan Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Jan Var[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Feb Plan[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Feb Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Feb Var[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Jan Plan[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Jan Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Jan Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Feb Plan[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Feb Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Feb Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Jan Plan[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Jan Act[/TD]
[TD="align: right"]350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Jan Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Feb Plan[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Feb Act[/TD]
[TD="align: right"]350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Feb Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I have a long string of data that is laid out horizontally and I am trying to find a way (without VBA) to relatively easily convert it to be vertically stacked. Below is a small example of what I need to be able to do on a much larger scale.
If I set up the horizontal spreadsheet to have the 6 rows for each 1 row of data on the horizontal, is there a formula that allows me to look up a combination of styles and types from the vertical table and grab them from both the column and row in the horizontal table?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 519"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Department[/TD]
[TD]Category[/TD]
[TD]Style[/TD]
[TD]Jan Plan[/TD]
[TD]Jan Act[/TD]
[TD]Jan Var[/TD]
[TD]Feb Plan[/TD]
[TD]Feb Act[/TD]
[TD]Feb Var[/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Department[/TD]
[TD]Category[/TD]
[TD]Style[/TD]
[TD]Type[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Jan Plan[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Jan Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Jan Var[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Feb Plan[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Feb Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]ABC[/TD]
[TD]Feb Var[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Jan Plan[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Jan Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Jan Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Feb Plan[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Feb Act[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]DEF[/TD]
[TD]Feb Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Jan Plan[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Jan Act[/TD]
[TD="align: right"]350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Jan Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Feb Plan[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Feb Act[/TD]
[TD="align: right"]350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Men[/TD]
[TD]Outerwear[/TD]
[TD]GHI[/TD]
[TD]Feb Var[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]