How to combine two sets of data with one common column then combine multiple rows

[gc-804]

Hello,

I have two sets of large data. Below are simplified examples:

File 1:

Type	Code	Description	Box 1	Box 2	Box 3
Earning	A	AAA	11	22	33
Earning	B	BBB	11	22	33
Earning	C	CCC	11		33
Deduction	D	DDD
Deduction	E	EEE

File 2:

Description	Box - Other1	Box - Other2
CCC	40
DDD		50
EEE		60

I want to combine them into below:

Type	Code	Description	Box 1	Box 2	Box 3	Box - Other1	Box - Other2
Earning	A	AAA	11	22	33
Earning	B	BBB	11	22	33
Earning	C	CCC	11		33	40
Deduction	D	DDD					50
Deduction	E	EEE					60

I uses Joint by Description first and then Summarize by Description, but Box - Other1 and Box - Other1 only returned Null. Should I do it differently?

Thanks!

[CharlieS · Accepted solution]

Hi @gc-804 

Since your File 1 has all the records in the correct order that you want your output in, let's take advantage of that by assigning a Record ID for later. Now we can Join the files on the [Description] field, union the joined and un-joined results and sort to the original order with the Record ID. Then we can use a Data Cleansing tool takes care of the empty and null values in our output. At the end of this workflow, you could use a Select tool to remove the {Record ID] field that was added at the beginning, but I left it in there for now as an example. 

Check out the attached workflow for an example of this in action. 

[gc-804]

@CharlieS,

It works. Thank you!