I’ve got lots of data by month of products we’ve bought which come directly from our main wholesale supplier as an excel speardsheet. Each month is a long list with product name, quantity bought, code, price, total etc in rows. I want to combine the data for a year’s worth of months. Problem is not all the months have the same items on them, and if we don’t buy it that month, its not listed in the rows.
What’s an easy way of producing a year summary sheet, where all the items rows of the same product are added together, so I only have one row for each product bought, and none missed out?

Many thanks
Colin

Put all the data on a single sheet.
You may need to use a formula to extract the date as a month (nember or text)
Once you have this you can use a pivot table to summerize all the info intro a single table which will give you the items and the quantities by month and in total.

there are a number of ways to approach this that i can think of.

Do you have a definitive list of products? does the name ever change, and by change i am talking about spelling mistakes.

Simplest approach i can think of is 13 worksheets, 12 are simply copy and paste of each monthly sheet.
The 13th is where the totals are, the first column is a list of all products, across the top give each column a month as a header.

then use a vlookup to populate each product for each month.

Then if you add a total to the last column on worksheet 13 that will show how much of each product you have sold each month. But for this to work the product name must not change.

Thanks for both your answers.
mrmo – The names won’t change, each product has a unique wholesaler code. I like the idea of a yearly page – that’s what I want but I it to look the same as the other pages (rows with all the other info ) just for the amount column to reflect the total amount bought in the year. And is there a way to set it up so that if we started buying a new product, an entry for it would automatically appear in the yearly summary page?

Thanks again – I’ll go and have a play.

Colin