Charts using every other column

[gavinbox]

I've a file which is data from our POS till system.
Attached is apportion of it and I'd like to know how I can extract sales data that is useful.
I want to see only quantities sold, not value (for example column G, not column H).
How do I make a chart that would show me that for each product, and could I then make one for each category, or just products within.

Ideally I'll be able to simply bolt onto the end of one worksheet, new data as I extract it from the till, paste it on the end, and have the charts update automatically.

Thanks for any help,

Gavin

					Sat 02/01/2014	Sun 02/02/2014	Mon 02/03/2014	Tue 02/04/2014
Name	SKU	Barcode	Category	Subcategory	Quantity	Total	Quantity	Total	Quantity	Total	Quantity	Total
1kg Round Hill El Chollo	1E+09		RETAIL	Coffee
Alm croiss	11125	11125	FOOD	Breakfast	10	23.6	10	24	6	14	3	6.8
Choc croiss	11120	11120	FOOD	Breakfast	8	16	10	20	7	16	7	14
Egg & Coppa Bap	1E+09		FOOD	Breakfast	4	11.67	8	23.33	8	23.33	3	11.67
Egg & Veg Bap	1E+09		FOOD	Breakfast	3	8.5	2	5.67	3	8.5	4	11.33
HAM cheese F	11121	11121	FOOD	Breakfast	1	2.7	2	5.4	1	2.7
Ham cheese T	11126	11126	FOOD	Breakfast	7	15.75	2	4.5	3	6.75	4	9
Plain croiss	11124	11124	FOOD	Breakfast	6	9.6	7	10.67	6	9.33	7	10.67
TOMATO cheese F	1E+09		FOOD	Breakfast					2	4.8	2	4.8
TOMATO cheese T	1E+09		FOOD	Breakfast	5	10	4	8	2	4	2	4
Yog Gran	11122	11122	FOOD	Breakfast	6	14.5	7	16.92	8	19.33	3	10.67
Cake small	11129	11129	FOOD	Cakes							1	2.5
Doughnut	11133	11133	FOOD	Cakes	17	44.17	5	12.5
Frangipane	11131	11131	FOOD	Cakes							4	10.4
Loaf	11128	11128	FOOD	Cakes	5	11.5	20	46	18	39.87	11	25.3
Mandarin Choc	11132	11132	FOOD	Cakes	1	2.9

<colgroup><col span="13"></colgroup><tbody>
</tbody>

 

[sijpie]

I think the easiest is to use a user defined function (UDF) to get your sums for every other column.

To create our UDF, press Alt-F8.
in the dialog box at the top type in 'SumEvery2Columns' (without the ')
The button to the right will change to a 'Create' button.
Press it. The VBA editor opens with a skelton macro in the right pane.

Delete everything and replace it with:

<See post below for the correct function>

If your product name is in column A, and your first date starts in column G then insert a column infront of G, so that the first date now starts in F.
Now in G3 (your first product row) put:
=SumEvery2Columns(F3)
copy the formula down for all your products.

Now you can use this for graphing your product sales. if you add a new date, the sums will automatically be updated

 

[sijpie]

Sorry, noticed an error in the function.

So put this function in the VBA editor:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><br><br><SPAN style="color:#00007F">Function</SPAN> SumEvery2Columns(rStart <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#007F00">'returns the sum of every other cell on the row of rStart _<br> and starting at rStart.</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Usage: =SumEvery2Columns(F4)</SPAN><br><SPAN style="color:#007F00">'   this will give the sum of F4, H4, J4, L4, etc to the last column in use</SPAN><br><SPAN style="color:#007F00">'   Text is ignored</SPAN><br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> lC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lCLast <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.Volatile<br>    <SPAN style="color:#00007F">With</SPAN> rStart<br>        lCLast = .Parent.Cells(.Row, Columns.Count).End(xlToLeft).Column<br>        <br>        <SPAN style="color:#00007F">For</SPAN> lC = 0 <SPAN style="color:#00007F">To</SPAN> lCLast - .Column <SPAN style="color:#00007F">Step</SPAN> 2<br>            <SPAN style="color:#00007F">If</SPAN> IsNumeric(.Offset(0, lC)) <SPAN style="color:#00007F">Then</SPAN><br>                SumEvery2Columns = SumEvery2Columns + .Offset(0, lC)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> lC<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>