Categorizing data

[Anomandaris]

Hi guys,

just when i thought i had it all done, i'm told i need things in a different format.....so here's what I'm trying to do


I will have data similar to the one on Sheet1, and I need to send it to another sheet and look like Sheet2.

Lot means quantity

The codes basically represent months, eg Z8 is December 2008, H9 is MArch 2009.........so I need to first separate the trades by product, then by Buy/Sell, then by month Code......showing all the different individual trades by lots,price, (lot*price), then showing the total and average price for each monthcode.

Way too complicated for me to figure out, as there are so many products and number of trades vary here, I've only given example of 3 or 4 for each month but it could be 100. Also there could be several monthcodes for each product.

I got my fingers crossed. If you guys can help, it'd be awesome!
Thanks in advance, and i apologize if it wastes too much of your time.

[Anomandaris]

Not to sound impatient, I understand this can be quite long and complicated. But I was just wondering if anyone is actually working on it. It would just give me a sense of relief.

No hurry, take your time

Thanks

[Bob Phillips]

That's a nasty format.

Why not just add a column for qty * price, and pivot it?

[Anomandaris]

yeah man i know, its very annoying one, pesky clients u know...
but thats a good idea, I'm trying the pivot thing now see how similar i can make it look, i think that may just work out.

Once I have a pivot format set up on one sheet2, Is there a macro code that would fill it up from data on sheet1? (Data on sheet1 will keep changing)

thanks mate

[Bob Phillips]

You should create a dynamic named range for the data and pivot that, that way it will always be using all the data and will just need a refresh.

[Anomandaris]

dude these pivot tables are trouble, I cant get the right information in the right place.....like I needed an average for each section but it doesnt know how to calculate that and I cant even throw in a formula in the table. The average it calculates does not take the quantity into account, so its not 'weighted' properly.

Any ideas how that could be approached? This is an XP version unfortunately so doesnt have the better options available in 2007.

[Anomandaris]

Its ok I figured out how to get the weighted avg. into the pivot table, looks ugly though lol.

Thanks for your continued help xld