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 |
Solved! Go to Solution.
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 🙂
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..
Thanks to both of you. AndrewS the example was the key. Thank you!