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 CodeC1 EnabledManual C1Q2 EnabledManual Q2Status1Status2Column1Column2Column3Column4Info12Info34ABCFALSEFALSEFALSETRUENoNo123FALSEFALSETRUEProdYesBNMTRUEFALSETRUETRUEAutoManual123FALSETRUETRUE DFGTRUETRUETRUEFALSEManualAuto EGH 789FALSEFALSETRUE GHJ NULLTRUETRUEFALSE KLZ NULLTRUETRUEFALSE LOP TESTINGNOMNB TESTINGNOZXCFALSETRUEFALSEFALSENoNo789TRUEFALSEFALSE

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

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

TRUE

BNM

TRUE

FALSE

TRUE

Auto

Manual

DFG

TRUE

FALSE

Manual

Auto

ZXC

FALSE

TRUE

FALSE

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

TRUE

123

FALSE

TRUE

Prod

Yes

BNM

TRUE

FALSE

TRUE

Auto

Manual

123

FALSE

TRUE

DFG

TRUE

FALSE

Manual

Auto

EGH

789

FALSE

TRUE

GHJ

NULL

TRUE

FALSE

KLZ

NULL

TRUE

FALSE

LOP

TESTING

MNB

TESTING

ZXC

FALSE

TRUE

FALSE

789

TRUE

FALSE

Output

Join

Accepted answers

AndrewS

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..

JoinMultiple.yxmd

All comments

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

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..

JoinMultiple.yxmd

tcwildhart

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