ezpzspreadsheets
New Member
- Joined
- Dec 17, 2017
- Messages
- 2
I have an array of a sales report that was a .csv. I ran the text to columns and am left with this. I named the data set A2:F6 as "MYDATA"
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane
[/TD]
[TD]Shirt[/TD]
[TD]Leggings[/TD]
[TD]Dress[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Edith[/TD]
[TD]Leggings[/TD]
[TD]Skirt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cora[/TD]
[TD]Leggings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sallie[/TD]
[TD]Skirt[/TD]
[TD]Dress[/TD]
[TD]Dress[/TD]
[TD]Leggings[/TD]
[TD]Shirt[/TD]
[/TR]
</tbody>[/TABLE]
I want the data to look like this
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]Shirt[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jane[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Edith[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Edith[/TD]
[TD]Skirt[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Cora[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sallie[/TD]
[TD]Skirt[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Sallie[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Sallie[/TD]
[TD]Shirt[/TD]
[/TR]
</tbody>[/TABLE]
I've tried
INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)
to get the ITEM in one column, but am not sure how to carry over the Header information (names) in column A. Thanks in advance.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane
[/TD]
[TD]Shirt[/TD]
[TD]Leggings[/TD]
[TD]Dress[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Edith[/TD]
[TD]Leggings[/TD]
[TD]Skirt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cora[/TD]
[TD]Leggings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sallie[/TD]
[TD]Skirt[/TD]
[TD]Dress[/TD]
[TD]Dress[/TD]
[TD]Leggings[/TD]
[TD]Shirt[/TD]
[/TR]
</tbody>[/TABLE]
I want the data to look like this
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]Shirt[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jane[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Edith[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Edith[/TD]
[TD]Skirt[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Cora[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sallie[/TD]
[TD]Skirt[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Sallie[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Sallie[/TD]
[TD]Shirt[/TD]
[/TR]
</tbody>[/TABLE]
I've tried
INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)
to get the ITEM in one column, but am not sure how to carry over the Header information (names) in column A. Thanks in advance.