Splitting data from an existing delivery schedule

[arthurphil]

Hello,

We run a delivery schedule for products that are collected from various suppliers for delivery to various customers by various hauliers. We would like to determine the outcome of each load from our already existing delivery schedule;

Delivery schedule

Delivery schedule & key.jpg

On a separate sheet I would then like to recreate the data by using the Outcome column, so it would look something like this;

Data.jpg

I would then be able to use the data in the new sheet for various different reports.

Any help is and would be greatly appreciated.

Thanks for looking.

Phil

[Glenn Kennedy]

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

[Richard Buttrey]

Hi,

One way would be to add an extra column F "Outcome Description" to your data and add a VLOOKUP() in F2 copied down, e.g.

Please Login or Register  to view this content.

Then you could use a Pivot Table to group your data by the Outcome description.

[arthurphil]

Thank you, give me 5 minutes to create and download.

[arthurphil]

Data.jpg

Hi, hopefully this shows you what I'm after. To clarify, column F (outcome)in the Delivery schedule sheet is a new column with the key shown from B27.

Thank you,

[arthurphil]

Excel forum data 02.01.15.xlsx

There we go! Sorry for the delay.

[arthurphil]

Sheet 2 "Data" shows how I would potentially like the information to be automatically listed as an outcome (from the key) is added in column F.

[arthurphil]

Hopefully I have added what was required..

[Richard Buttrey]

Phil,

It's always a good idea if you're going to offer an example to make sure it's exactly as your production data otherwise we all waste our time when, as here, it seems that the original data is not quite like your original offering.

I'll take a further look shortly but at first glance you need to ensure you capture your data in a normalised 2D table if you want to achieve this analysis efficiently and without jumping through all sorts of esoteric function combinations.

[Richard Buttrey]

See attached.

Being a Pivot table you can drag and drop the field headers around to dice and slice to your hearts content. Perhaps drop the date filed into the Report Filter area along with the Delivery status so that you can easily show specific dates. Or perhaps choose to group your dates by Weeks. Strictly speaking you don't need the Total column unless you are going to group field values but I've shown it here for completeness.

[arthurphil]

Hi Richard, thank you for taking the time to respond.

Are there any ways of doing this without using a Pivot Table initially, so that the data is more "live" for other members of staff to see. By compiling the data into one spreadsheet, I would then be able to manipulate it as needed and generate specific Pivot Tables. That is unless my understanding of Pivot Tables being a one time snap shot of information is incorrect.

Thanks in advance,

[Richard Buttrey]

Hi Richard, thank you for taking the time to respond.

Are there any ways of doing this without using a Pivot Table initially, so that the data is more "live" for other members of staff to see. By compiling the data into one spreadsheet, I would then be able to manipulate it as needed and generate specific Pivot Tables. That is unless my understanding of Pivot Tables being a one time snap shot of information is incorrect.

Thanks in advance,

Maybe you are misunderstanding Pivot Tables. You couldn't be farther from the truth with your belief that they are a one time snap shot. Just the opposite in they give you the ability to view your data in just about every way you can think of - and more. And if you ensure your data is covered by a dynamic range name that expands or contracts AUTOMATICALLY as necessary as you add or delete data then nothing could be 'more live'. It would certainly pay youj to spend half an hour playing with them to understand what they can do.

[arthurphil]

It seems I need to reevaluate my understanding of Pivot Tables. I shall go away and do some homework... (shuffles away with tail between legs!)

Many thanks,