Pivot: 2 tables of data on diff sheets

I have 2 tables of data on diff sheets:

Mining Location

shift_start location_description
1/01/2006 Close ROM
1/01/2006 Close ROM
2/01/2006 Ramp 3 South, Strip 14
2/01/2006 Ramp 3 South, Strip 14
2/01/2006 Ramp 5 West, Strip 6 Seam 4
3/01/2006 Ramp 5 West, Strip 6 Seam 4
3/01/2006 Ramp 3 North, Strip 14
3/01/2006 Ramp 5 West, Strip 6 Seam 4
4/01/2006 Ramp 3 North, Strip 14
4/01/2006 Wash Plant Stockpile
4/01/2006 Ramp 5 West, Strip 6 Seam 4
4/01/2006 Wash Plant Stockpile
4/01/2006 Ramp 5 West, Strip 6 Seam 4
4/01/2006 Ramp 3 North, Strip 14
5/01/2006 Ramp 3 North, Strip 14
5/01/2006 Ramp 3 North, Strip 14
5/01/2006 Wash Plant Stockpile
5/01/2006 Far ROM
5/01/2006 Wash Plant Stockpile
5/01/2006 Ramp 5 West, Strip 6 Seam 4
etc, etc


Train Data

train_number. Date Loaded
00000001 1/01/06
00000002 2/01/06
00000003 3/01/06
00000004 4/01/06
00000005 5/01/06
etc, etc


obviously there is a load of data (1400 rows for the locations table and 1000 for the train data).

Question 1: Given that there can be up to 6 locations/shift, I would like to line up the data such that the resulting table on a seperate sheet is like this:

train_number. shift_start location_description
00000001 1/01/2006 Close ROM
________ ________ Close ROM
00000002 2/01/2006 Ramp 3 South, Strip 14
________ ________ Ramp 3 South, Strip 14
________ ________ Ramp 5 West, Strip 6 Seam 4
00000003 3/01/2006 Ramp 5 West, Strip 6 Seam 4
________ ________ Ramp 3 North, Strip 14
________ ________ Ramp 5 West, Strip 6 Seam
etc, etc

**Underscores are denoting blank cells**


There is a third sheet of data:

ship_number. train_number
____ABC1 00000001
________ 00000002
________ 00000003
________ 00000004
________ 00000005
etc,etc


Then question 2: Can it be consolidated into this?

ship_number. train_number location_description
____ABC1 00000001 Close ROM
________ ________ Close ROM
________ 00000002 Ramp 3 South, Strip 14
________ ________ Ramp 3 South, Strip 14
________ ________ Ramp 5 West, Strip 6 Seam 4
________ 00000003 Ramp 5 West, Strip 6 Seam 4
________ ________ Ramp 3 North, Strip 14
________ ________ Ramp 5 West, Strip 6 Seam

I cannot immediately see how the train and location data links.

Originally Posted by robert111

I cannot immediately see how the train and location data links.

Shift_start = Date Loaded

Originally Posted by starguy

Hi,
its better that you attach a sample workbook for proper consideration.

Having difficulty doing that... no idea why...

Originally Posted by oldchippy

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this to your post.

*crosses fingers*

hope this works

so its not possible then??

This can be achieved with a combination of Microsoft Query and Pivot Tables. Use Microsoft Query from within Excel via External Data Sources (from the Pivot Table option). Select the 3 tables of data within the query and join on Shift_start and date_loaded for the first two tables, then train_number with train_number on tables 2 and 3. When you return the data to Excel as a Pivot table, place the ship number, train number, shift start and location description as Row fields and place the ship number within the data area.

This should give you what your looking for. I tested it with your sample data and I replicated what you had.

Originally Posted by Gary Brown

This can be achieved with a combination of Microsoft Query and Pivot Tables. Use Microsoft Query from within Excel via External Data Sources (from the Pivot Table option). Select the 3 tables of data within the query and join on Shift_start and date_loaded for the first two tables, then train_number with train_number on tables 2 and 3. When you return the data to Excel as a Pivot table, place the ship number, train number, shift start and location description as Row fields and place the ship number within the data area.

This should give you what your looking for. I tested it with your sample data and I replicated what you had.

Cheers for that mate.

You wouldnt be able to upload that file for me please?

sorry for the bump but if you could post a copy of the file that would be good. Gary (or anyone??)

Cheers, ck