Blending 2 Spreadsheets

[G_SAND]

Hi All

I have data from 2 spreadsheets I want to blend. There is common data between the 2 but I can't think of a way of getting the data the way I want it:

Master Spreadsheet:

Part1	Level	Part2	Field1
a	0	a	x
a		b	x
a		c	x
a		d	x
a		e	x

 This data is repeated in a similar fashion for multiple parts

Spreadsheet 2

Level	Part2	Field1
0	d	y
	d1	y1
	d2	y2
	d3	y3
0	e	z
	e1	z1
	e2	z2
	e3	z3

Parts d and e are the same as those in the master spreadsheet. I need the header added to SS 2 (which I think is reasonably simple) but then want the data for the parts in SS 2 entered under the parts in the master:

Part1	Level	Part2	Field1
a	0	a	x
a		b	x
a		c	x
a		d	x
a	0	d	y
a		d1	y1
a		d2	y2
a		d3	y3
a		e	x
a	0	e	z
a		e1	z1
a		e2	z2
a		e3	z3

I need to keep both the d and e part numbers from both sheets so they will be doubled up and the 'zeros' in SS2 appear at the start of each of the data sets.

Hope that makes sense.

Thanks

[NickC]

Hello,

In order to achieve this output you will need to use the Union tool.  Within the Union tool you will have the option to manually configure the fields. An If statement formula will replace the null values for Part1.  

If Isnull([Part1]) then 'a' else [Part1] ENDIF

Then a sort on Part 2 Ascending will solve the order.

Please see v11.3 workflow attached.

[mborriero]

@G_SANDhow would you manage the Part1? is it always "a"? or it depends the part2?

If it is always "a" then I agree with @NickC.

[G_SAND]

Hi. I think I was being a bit too simplistic with my example tables. Here are some more appropriate versions:

SS1:

Header	Component	Description	Status	Schedule
101	1048762925	Steve	0	18
101	1087611087	Liz	1	2
101	1087611103	Mike	2	1
101	1088176992	Glyn	3	566351
101	1087981264	Dee	4	566351
101	1087873834	Albert	3	566351
101	1087873444	Tilly	3	566351
XXX	NEXT SET	OF DATA	0	18

This is my master spreadsheet. There are hundreds of these sequences all with different headers with Status 0 denoting the start of each one.

SS2:

Component	Description	Status	Schedule
1087873834	Albert	0	FRM
1088380666	Molly	1	A
1088463974	Izzie	1	D
1088381887	Poppy	1	D
1088381887	Poppy	2	D
1087873444	Tilly	0	FRM
1088477401	Sasha	1	C
1088556066	Mini	2	A
1088056547	Lola	1	D

This is the data I want to add to SS1. Again, the start of each data section starts with Status 0.

Albert from SS2 and the data below (up to the next Status 0) needs to slot into SS1 under the Albert that is already there but retaining the info next to it.

Then Tilly from SS2 needs the same thing.

The header needs to be added as well.

 Outcome (hopefully!):

Header	Component	Description	Status	Schedule
101	1048762925	Steve	0	18
101	1087611087	Liz	1	2
101	1087611103	Mike	2	1
101	1088176992	Glyn	3	566351
101	1087981264	Dee	4	566351
101	1087873834	Albert	3	566351
101	1087873834	Albert	0	FRM
101	1088380666	Molly	1	A
101	1088463974	Izzie	1	D
101	1088381887	Poppy	1	D
101	1088381887	Poppy	2	D
101	1087873444	Tilly	3	566351
101	1087873444	Tilly	0	FRM
101	1088477401	Sasha	1	C
101	1088556066	Mini	2	A
101	1088056547	Lola	1	D

I hope this makes it clearer.

Thanks

[mborriero]

Hi, I still do not understand how you get header. You say that you have multiple headers. How do you get header 101 for component that are in SS2 but not in SS1?

for example

1088056547

Lola

1

D

why in your final output has header 101 and not 102?

can you please attached an example with more headers?

[G_SAND]

Hi, I thought this might be a complicated 1! I'll try again with another example.

Hypothetical:

My company makes CAKE1 and CAKE 2 and here are the ingredients:

Header	Component	Description	Status	Schedule
CAKE 1	1048762925	Icing	0	18
CAKE 1	1087611087	Butter	1	2
CAKE 1	1087611103	Flour	2	1
CAKE 1	1088176992	Eggs	3	566351
CAKE 1	1087981264	Milk	4	566351
CAKE 2	1054789416	Flour	0	18
CAKE 2	1074893147	Fruit	1	2
CAKE 2	1039587468	Caster Sugar	2	1
CAKE 2	1015987557	Butter	3	566351
CAKE 2	1069874501	Mixed Nuts	4	566351

The supplier for the Eggs, Milk and Mixed nuts has information about what they need:

Component	Description	Status	Schedule
1088176992	Eggs	0	FRM
1088380666	Chickens	1	A
1088463974	Feed	1	D
1087981264	Milk	0	FRM
1088477401	Cows	1	C
1088556066	Grass	2	A
1069874501	Mixed Nuts	0	FRM
1033578917	Brazil	1	C
1044478489	Walnut	3	A

They supply these to loads of people so have no idea how it is going to be used so can't have a header. I want a full recipe for my cake so I want the vendors details below the relevant parts in my spreadsheet:

Header	Component	Description	Status	Schedule
CAKE 1	1048762925	Icing	0	18
CAKE 1	1087611087	Butter	1	2
CAKE 1	1087611103	Flour	2	1
CAKE 1	1088176992	Eggs	3	566351
CAKE 1	1088176992	Eggs	0	FRM
CAKE 1	1088380666	Chickens	1	A
CAKE 1	1088463974	Feed	1	D
CAKE 1	1087981264	Milk	4	566351
CAKE 1	1087981264	Milk	0	FRM
CAKE 1	1088477401	Cows	1	C
CAKE 1	1088556066	Grass	2	A
CAKE 2	1054789416	Flour	0	18
CAKE 2	1074893147	Fruit	1	2
CAKE 2	1039587468	Caster Sugar	2	1
CAKE 2	1069874501	Mixed Nuts	4	566351
CAKE 2	1069874501	Mixed Nuts	0	FRM
CAKE 2	1033578917	Brazil	1	C
CAKE 2	1044478489	Walnut	3	A

The vendors info is associated with either CAKE1 or CAKE2 so I need those headers somehow added to the relevant rows.

[mborriero]

Try this and let me know if it works. The output it is not in the same order, but it is really close.

I hope it is ok, if not I will try to sort it in the correct way but it is quite challenging.

[G_SAND]

Thanks for your help on this one but unfortunately I does have to match the output.

I was just about to start putting the 2 data sheets together manually so I'll carry on with that for now.

Don't spend too much time on this if it is too difficult.

Thanks again

[mborriero · Accepted solution]

Ok, have a try now. It seems to be working but it need to be tested with more data or a different example.

I love this challenge :)

[G_SAND]

Hi.

That works or this example so I am confident we can get it going for my actual data.

I'll put a selection of it together, as it does get a bit more complicated, and run it through this workflow and see what I get. 

Thanks for your help and I'll update the post if I get it to work for my full data set.