GaryK

OK I have some sales transactions that show a hole bunch of invoices for sales ledger accounts. Now there could be 20,30,50 entries for each account so I wan t to 'roll-up' the data to summarise. So say the data is

AccountRef,Nett,Vat
001,10001,78.50,12.50
001,10011,122.50,13.20
001,12300,10,1.75

I want it to group all 001 entries together and total the nett and vat columns, it would also be useful to be able to get excel to provide a count of the number of rows where 001 appears. I know I can filter the data but that is manual I need it to automatically process all accounts, how do I do it?
Cheers
Gary

ChefDude

pivot tables my good man

group by '001' column, sum net and vat columns.

in the pivot table dialogue, press the [Layout] button to sort this bit out.

GaryK

Thanks Chef, I had briefly look at pivots but couldnt make head nor tail!
Cheers
Gary

ChefDude

does this help?

GaryK

Thanks for that chef, although as soon as I drag a field into the section marked 'Drop Data Items Here' rather than let me drag more fields it instantly changes the spreadsheet showing data from the first field. I'll show you what I mean by these two pics:

http://www.business-answers.net/images/pivot1.JPG
http://www.business-answers.net/images/pivot2.JPG

Thanks again for your help, think I need to get hold of a book called pivot tables for f*ckwits that might help

Gary

ChefDude

nah, use the wizard in the Data menu. it's all interactive. just select your data cells (including title row).