How to Join/Concatenate Fields from Two Sources (One with Multiple Identifier Columns)

[sblank56]

Hello,

I'm new to Alteryx so I apologize if this is a simple question, I'm just not sure exactly what the best way to proceed with this is, but basically I'm just wondering if it was possible to take two sources and concatenate/join the 'Fund' and 'Cusip' columns and create a simple summary of just the FundCusip and Share amounts as per the example below- the caveat being the second source actually contains four different Cusip columns, of which only some or all of the fields might be populated 

I'm probably not describing this well, but ideally this is how I'd like the end result to look like:

Source1:

Fund	Cusip	Shares
A	ABC	1000
A	DEF	2000
B	DEF	3000
C	GHI	4000
D	JKL	2

Source2:

Fund	Cusip1	Cusip2	Cusip3	Cusip4	Shares
A	ABC				1000
A		DEF	DEF		2001
B				DEF	3000
C	GHI	GHI	GHI	GHI	4000
E		MNO			15

Ideal End Result:

FundCusip	Shares_Left	Shares_Right
A-ABC	1000	1000
A-DEF	2000	2001
B-DEF	3000	3000
C-GHI	4000	4000
D-JKL	2	NULL
E-MNO	NULL	15

Would anyone have a suggestion of what the best route to go about solving this would be?  I was thinking maybe just applying a Join tool to the two Input files, but haven't much luck yet.

Any help would be greatly appreciated! 

Thank you for your time,
Steve

[jdunkerley79 · Accepted solution]

Making the assumption that all the Cusip1,2,3 or 4 values are either the same or blank.

I would use a transpose tool and then a summarize tool to convert source 2 to look more like source 1.

After this, I would use a join tool followed by a union tool to merge the two tables

Sample attached