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
<tbody>
</tbody>
Sales Tab
<tbody>
</tbody>
<tbody>
</tbody>
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
Store A | Store B | Store C | Store D | |
Model 1 | 1 | 2 | 3 | 4 |
Model 2 | 1 | 2 | 3 | 4 |
Model 3 | 1 | 2 | 3 | 4 |
Model 4 | 1 | 2 | 3 | 4 |
<tbody>
</tbody>
Sales Tab
Store A | Store B | Store C | Store D | |
Model 1 | 1 | 2 | 1 | 2 |
Model 2 | 1 | 2 | 1 | 2 |
Model 3 | 1 | 2 | 1 | 2 |
Model 4 | 1 | 2 | 1 | 2 |
<tbody>
</tbody>
Store | Model | SOH | Sales |
Store A | Model 1 | 1 | 1 |
Store A | Model 2 | 1 | 1 |
Store A | Model 3 | 1 | 1 |
Store A | Model 4 | 1 | 1 |
Store B | Model 1 | 2 | 2 |
Store B | Model 2 | 2 | 2 |
Store B | Model 3 | 2 | 2 |
Store B | Model 4 | 2 | 2 |
Store C | Model 1 | 3 | 1 |
Store C | Model 2 | 3 | 1 |
Store C | Model 3 | 3 | 1 |
Store C | Model 4 | 3 | 1 |
Store D | Model 1 | 4 | 2 |
Store D | Model 2 | 4 | 2 |
Store D | Model 3 | 4 | 2 |
Store D | Model 4 | 4 | 2 |
<tbody>
</tbody>