Help with my pivot table Excel 2010

[Redlittle]

I work for a retail company that sells many different items and I am trying to list our sales over a period of years for the different type of goods that we sell, meats, breads, clothing and sweets etc.

Column A is a list of my of different categories and column B onwards is the calendar year starting at 2006 – 2015 now as the data was first prepared using week numbers each year will repeat itself a 52 times.

When I prepare my pivot table I end up with 2006 – 2015 and then I get 20062, 20072, 20063, 20073 and so on.

Is there a way that the pivot table will calculate each year as one?

Cheers in advance.

[ben_hensel]

It sounds like what's actually stored in the cell is a 5/6 digit number, with the year as the left four digits and the week number as the right two-three digits.

My advice is to insert helper columns and split out the data as two different columns; where A1 is the serial number, helper columns with =--LEFT(A1, 4) and =--MID(A1, 5,2) as the formula in them for example.

Then you can run your Pivot Table off the helper columns instead.

[Izandol]

If you must combine columns you must create calculated field for every year or you must change data structure with one column for year number.

[Redlittle]

Sussed it, I brought all of the data into one worksheet by using "sumif".

Thanks for your help.