Help with data manipulation

[Samsonuk]

Hi I have imported some raw data on sales and need to manipulate it so that i have 1 column for Date, 1 for sales and 1 for quantity.

So i'm trying to create a macro that inserts new rows, inserts 3 new columns "date,sales,qty", then transponses my values down.

I've made a demo spreadsheet showing what i need!

I'm new to VBA, managed to record a macro that did it for one product but i cant do it for more than one because my relative references screw up.

Here's a link to dropbox...https://dl.dropboxusercontent.com/u/43697423/Macro HELP.xlsx


If anyone can help me or guide me into what i need to do that would be great!

Sam

 

[adam087]

Hi Sam

I've not looked at your sheet. Would you be able to post a sample of how the data currently looks here? There's some tools for doing so in my signature.


/AJ

 

[Samsonuk]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Store Name	Product Item Nbr	Product Name	Data Type	Store Type	14/06/2014	21/06/2014	28/06/2014	05/07/2014	12/07/2014	19/07/2014	26/07/2014	02/08/2014	09/08/2014	16/08/2014	23/08/2014	30/08/2014	06/09/2014
2	Store 1	5504368	Product 1	Sales	Visiting Store	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£66.82	£72.50	£65.00	£109.51	£93.20
3	Store 1	5504368	Product 1	Qty	Visiting Store	0	0	0	0	0	0	0	0	26	29	26	47	40
4	Store 1	5528489	Product 5	Sales	Visiting Store	£219.27	£492.19	£777.71	£458.97	£20.86	£25.50	£18.88	£17.88	£14.90	£22.35	£17.88	£11.92	£154.85
5	Store 1	5528489	Product 5	Qty	Visiting Store	261	593	937	549	14	18	14	12	10	15	12	8	181
Data

I think this is it....

 

[Samsonuk]

And this is the target format i'm looking for...

Excel 2012
	A	B	C	D	E	F	G
1	Store Name	Product Item Nbr	Product Name	Store Type	Date	Sales	Qty
2	Store 1	5504368	Product 1	Visiting Store	14/06/2014	£0.00	0
3	Store 1	5504368	Product 1	Visiting Store	21/06/2014	£0.00	0
4	Store 1	5504368	Product 1	Visiting Store	28/06/2014	£0.00	0
5	Store 1	5504368	Product 1	Visiting Store	05/07/2014	£0.00	0
6	Store 1	5504368	Product 1	Visiting Store	12/07/2014	£0.00	0
7	Store 1	5504368	Product 1	Visiting Store	19/07/2014	£0.00	0
8	Store 1	5504368	Product 1	Visiting Store	26/07/2014	£0.00	0
9	Store 1	5504368	Product 1	Visiting Store	02/08/2014	£0.00	0
10	Store 1	5504368	Product 1	Visiting Store	09/08/2014	£66.82	26
11	Store 1	5504368	Product 1	Visiting Store	16/08/2014	£72.50	29
12	Store 1	5504368	Product 1	Visiting Store	23/08/2014	£65.00	26
13	Store 1	5504368	Product 1	Visiting Store	30/08/2014	£109.51	47
14	Store 1	5504368	Product 1	Visiting Store	06/09/2014	£93.20	40
15	Store 1	5528489	Product 5	Visiting Store	14/06/2014	£219.27	261
16	Store 1	5528489	Product 5	Visiting Store	21/06/2014	£492.19	593
17	Store 1	5528489	Product 5	Visiting Store	28/06/2014	£777.71	937
18	Store 1	5528489	Product 5	Visiting Store	05/07/2014	£458.97	549
19	Store 1	5528489	Product 5	Visiting Store	12/07/2014	£20.86	14
20	Store 1	5528489	Product 5	Visiting Store	19/07/2014	£25.50	18
21	Store 1	5528489	Product 5	Visiting Store	26/07/2014	£18.88	14
22	Store 1	5528489	Product 5	Visiting Store	02/08/2014	£17.88	12
23	Store 1	5528489	Product 5	Visiting Store	09/08/2014	£14.90	10
24	Store 1	5528489	Product 5	Visiting Store	16/08/2014	£22.35	15
25	Store 1	5528489	Product 5	Visiting Store	23/08/2014	£17.88	12
26	Store 1	5528489	Product 5	Visiting Store	30/08/2014	£11.92	8
27	Store 1	5528489	Product 5	Visiting Store	06/09/2014	£154.85	181
Target Format