Combine Multiple Input Files - Primary Key Numbers Different Across XLS Files

[tcwildhart]

I am attempting to combine multiple spreadsheets that all have a key field, but not each spreadsheet has the same number of the primary key data fields. In some cases they have the same number of ‘CODE’ and in some cases not. With all 3 combined, I would like to have FINAL.XLS. I understand that the blank fields will come across as NULL – I will convert those to ‘No Data’.

1a.xls

Code

C1 Enabled

Manual C1

Q2 Enabled

Manual Q2

Status1

Status2

ABC

FALSE

FALSE

FALSE

TRUE

No

No

BNM

TRUE

FALSE

TRUE

TRUE

Auto

Manual

DFG

TRUE

TRUE

TRUE

FALSE

Manual

Auto

ZXC

FALSE

TRUE

FALSE

FALSE

No

No

2b.xls
Code	Column1	Column2	Column3	Column4
ABC	123	FALSE	FALSE	TRUE
BNM	123	FALSE	TRUE	TRUE
EGH	789	FALSE	FALSE	TRUE
GHJ	NULL	TRUE	TRUE	FALSE
KLZ	NULL	TRUE	TRUE	FALSE
ZXC	789	TRUE	FALSE	FALSE

3c.xls
Code	Info12	Info34
ABC	Prod	Yes
LOP	TESTING	NO
MNB	TESTING	NO

This is the end result I am looking to achieve 

FINAL.XLS

Code

C1 Enabled

Manual C1

Q2 Enabled

Manual Q2

Status1

Status2

Column1

Column2

Column3

Column4

Info12

Info34

ABC

FALSE

FALSE

FALSE

TRUE

No

No

123

FALSE

FALSE

TRUE

Prod

Yes

BNM

TRUE

FALSE

TRUE

TRUE

Auto

Manual

123

FALSE

TRUE

TRUE

DFG

TRUE

TRUE

TRUE

FALSE

Manual

Auto

EGH

789

FALSE

FALSE

TRUE

GHJ

NULL

TRUE

TRUE

FALSE

KLZ

NULL

TRUE

TRUE

FALSE

LOP

TESTING

NO

MNB

TESTING

NO

ZXC

FALSE

TRUE

FALSE

FALSE

No

No

789

TRUE

FALSE

FALSE

[cmcclellan]

Isn't the primary key CODE in all cases ?

If so, you join 1a to 2b, then the result to 3c then a Select tool to order/rename/remove the fields and you're done 🙂 

Well, in your example you probably don't need a Select because you want all the fields 🙂 

[AndrewS · Accepted solution]

Hi @tcwildhart 

As mentioned by @cmcclellan Code appears to be your primary key based on the dataset provided.

You could use a join multiple tool outputting all records and then clean up the Code field. I've attached a sample workflow, hope this helps..

[tcwildhart]

Thanks to both of you. AndrewS the example was the key. Thank you!