Pull spefic data, sort & average it and put into seperate tables

Sheet1.jpgSheet1.jpg

I have (what I think) is a complicated problem here! I've always been successful with my problems on here so I'm hopeful! I want to use the data in the attached sheet, extract parts of it and organise it into something much more simple. Probably into another sheet within the same workbook.

I only want to take data from parts (customer or supplier side) which have not got anything next to it in either the supplier or customer side. The first example of this starts at row 21 where the supplier side has details but there is nothing in the customer side.

I would need 3 "tables" for each the supplier and customer side, organised into location. So 1 for North, 1 for South and 1 for Mids for both customer and supplier side - 6 in total. Within those tables I need it organising into products, whatever that might be. I'd then like it organising into monthly period's with an average price for each month.

My aim is to have a sheet that at a glance tells me you need to buy/sell x number of tonnes for these months of this product in the north/south/mids.

I know I'm asking a lot but if there is anyone who thinks they can help me set it up or knows of anybody that could offer this service I'd be massively greatful. I understand that this sort of service might not come for nothing.

Thanks,

Ant

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi, Can you post a sample of your workbook ?

Thanks for changing the title.

It's really hard to visualise what you're trying top achieve. The image attachments don't work for me, and I wouldn't be able to work with them, anyway: really need a sample workbook to look at:



Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

One more question, before I look at your attachment - your profile says you use Excel 2003, but your attachment is in >2007 format. Which version are you actually using?

(I ask to make sure I don't recommend a solution which uses functionality not available in your Excel version)

Attached is a starting point. I've restructured your "Data" sheet slightly, to give each column a unique header, added a helper "Status" column to identify the row status, then created pivot table reports insheet "Report".

Far from a finished product, but a start you can work with, maybe?

Have you tried refreshing the pivot tables?!

Some broad questions there. I'd recommend researching pivot tables, so you understand them better (and things like refresh on open options) - Then try things out, to see how they work. When you change a column header, it will change the field name in the pivot table, so the pivot table may need to be adjusted to reflect your change. New values in a field (e.g. a new area) will be included when the pivot table is refreshed.