Managing a large amount of data

[Jlister3]

Hi There,


I hope someone will be able to give me some tips!


I have 52 worksheets (one for each week of the year) each with about 6k lines on.


Each sheet has a list of products and their sales for that week. I am trying to create one master sheet with a total sale for each product. I have 60 different products.


The data is listed in the following way:


Product Sales
A 1
A 1
A 3
A 2
B 5
B 1
B 3
C 2


As every single sale is listed rather than just a total: Product A: 7 Product B:9 Product C:2. I am struggling to organise it.


I have been subtotalling by product name and then putting into a pivot table and then sort it from there. Is there a quicker/better way to summarise this data?


I have also tried consolidating the data but this doesn't seem to work due to fact that each sale is listed individually. Rather than just a summary of product A's sale etc....


I hope someone can assist!

 

[RoryA]

Welcome to the forum.

Please take a minute to read the forum rules, especially the one about cross-posting, and then add the relevant links. Thanks!

 

[My Aswer Is This]

Are you saying the product name is in column A and number of sales is in column B ?

 

[Jlister3]

Hi Thanks for getting back to me!

So the data is actually in column F and the sales in I. But for example say it was t-shirts I was selling, its listed in the following way. A line for each sale made rather than just a total of 'Red T-shirts' for that week


[TABLE="width: 360"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Country[/TD]
[TD]Value[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]Red T-shirt[/TD]
[TD]UK[/TD]
[TD]£20.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Red T-shirt[/TD]
[TD]UK[/TD]
[TD]£30.00[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Red T-shirt[/TD]
[TD]France[/TD]
[TD]£10.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Red T-shirt[/TD]
[TD]UK[/TD]
[TD]£40.00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Blue T-shirt[/TD]
[TD]UK[/TD]
[TD]£24.00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Blue T-shirt[/TD]
[TD]Uk[/TD]
[TD]£12.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Blue T-shirt[/TD]
[TD]USA[/TD]
[TD]£12.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Yellow T-shirt[/TD]
[TD]UK[/TD]
[TD]£40.00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Yellow T-shirt[/TD]
[TD]UK[/TD]
[TD]£8.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Yellow T-shirt[/TD]
[TD]France[/TD]
[TD]£24.00[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Yellow T-shirt[/TD]
[TD]UK[/TD]
[TD]£8.00[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Please read post #2 and comply with it. Thank you.