How to combine datasets

[pjmorris]

I have three data sources that I need to consolidate into a single data source:

1. A list of maintenance periods which are uniquely (no repeats) identified by 4 letters and 3 digits, for example CANS118. The Maintenance Periods mentioned in the data sources below will be listed it this list. While any maintenance period can have multiple activities some may not have any activity detailed against it and so will not appear in either of the lists below. This first list looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Maintenance Period[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CANS118[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]OTAD118[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]WGND117[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]OTA123[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AAAD111[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]BBBC222[/TD]
[/TR]
</tbody>[/TABLE]


2. A list of activities that will occur in each maintenance period from source A. For example in two columns:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Maintenance Period[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CANS118[/TD]
[TD]A170012345[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CANS118[/TD]
[TD]A170012400[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]OTAD118[/TD]
[TD]A170012345[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]OTAD118[/TD]
[TD]A170016000[/TD]
[/TR]
</tbody>[/TABLE]

3. A list of activities that will occur in each maintenance period from source B. For example in two columns:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Maintenance Period[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CANS118[/TD]
[TD]B170067890[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CANS118[/TD]
[TD]B170012345[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]WGND117[/TD]
[TD]B170012345[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]OTAI123[/TD]
[TD]B170034520[/TD]
[/TR]
</tbody>[/TABLE]

I think I can do this rather clumsily by first copying the each list of activities against maintenance periods and then adding the list of maintenance periods. This doesn't seem to be a good use of excel's capabilities and I'm sure there is a more elegant solution, but despite various strategies involving pivot tables and establishing relationships I fail to make it work - I end up with every maintenance activity listed against every maintenance period - which is not helpful.

The outcome I'd like to achieve is this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Maintenance Period[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CANS118[/TD]
[TD]A170012345[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CANS118[/TD]
[TD]A170012400[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]OTAD118[/TD]
[TD]A170012345[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]OTAD118[/TD]
[TD]A170016000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]CANS118[/TD]
[TD]B170067890[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]CANS118[/TD]
[TD]B170012345[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]WGND117[/TD]
[TD]B170012345[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]OTAI123[/TD]
[TD]B170034520[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]AAAD111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]BBBC222[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I'd be grateful for any suggestions as to the approach that could be adopted.

 

[Alice Morland]

Hi PJ,

Did you try adding all your three sources in the Data Model, then establishing relationships? If you have unique codes, it shouldn't be a problem.

Then all you have to do is create a pivot table; in it, you can mix & match data from all your three sources.

If you're not sure how to do that, send me the link to a sample file and I'll create the model for you.

Alice

 

[pjmorris]

hi Alice,

I tried that by creating named tables, then joining them using the relationships manager in the data ribbon, followed by using a connection in the Pivotable dialogue. All seems to work, but it doesnt include any maintenance periods with no activities and maintenance periods that do have activities show all activities against every maintenance period. I cant post a link to the data as its on a private network. But if you try the example here https://www.intheblack.com/articles/2015/10/01/easy-steps-to-make-data-relationships-work-in-excel and then on the sales sheet change one of the 'Widgets' to 'Broom' and another to 'Bucket' you'll see that the output includes 'Widgets', 'Gadgets, 'Buckets' and 'Brooms' against every state, which is unhelpful.

Hope you can suggest what I'm doing wrong.

Regards