mattchewie
New Member
- Joined
- Sep 17, 2018
- Messages
- 4
Hi all,
I have data on 3 different tabs. 1 for stock on hand, 1 for sales, and i need to convert them to the the way i have it on the "Data for Pivot" tab. The data i have cannot be changed as this is the format i get it in. I basically want to combine SOH and sales by store, but then be able to have it in a line listing for a pivot table.
I have tried transposing it which works, but i need the line duplicated to show all models. The real data i have has around 200 stores, and 500 models, so i need a dump and process method... ideas?
an example below:
Stock on hand tab
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Store A[/TD]
[TD="class: xl63, width: 64"]Store B[/TD]
[TD="class: xl63, width: 64"]Store C[/TD]
[TD="class: xl63, width: 64"]Store D[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 2[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 3[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 4[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
Sales Tab
[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Store A[/TD]
[TD="width: 64"]Store B[/TD]
[TD="width: 64"]Store C[/TD]
[TD="width: 64"]Store D[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Model 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Model 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Model 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]Store[/TD]
[TD="width: 64"]Model[/TD]
[TD="width: 64"]SOH[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
I have data on 3 different tabs. 1 for stock on hand, 1 for sales, and i need to convert them to the the way i have it on the "Data for Pivot" tab. The data i have cannot be changed as this is the format i get it in. I basically want to combine SOH and sales by store, but then be able to have it in a line listing for a pivot table.
I have tried transposing it which works, but i need the line duplicated to show all models. The real data i have has around 200 stores, and 500 models, so i need a dump and process method... ideas?
an example below:
Stock on hand tab
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Store A[/TD]
[TD="class: xl63, width: 64"]Store B[/TD]
[TD="class: xl63, width: 64"]Store C[/TD]
[TD="class: xl63, width: 64"]Store D[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 2[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 3[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Model 4[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
Sales Tab
[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Store A[/TD]
[TD="width: 64"]Store B[/TD]
[TD="width: 64"]Store C[/TD]
[TD="width: 64"]Store D[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Model 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Model 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Model 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]Store[/TD]
[TD="width: 64"]Model[/TD]
[TD="width: 64"]SOH[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]Model 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]Model 4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]Model 4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]Model 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]