Duplications created by Join tool

[JSL99]

Hi Everyone,

I am joining 7 tables together and am encountering some duplication problems. I have created some simplified tables below to give an idea.

ID	Label_1
1	A
2	B
3	C
4	D
5	E

ID	Label_2
2	F
2	G
3	H
5	I

ID	Label_3
2	J
2	K
2	L
4	M
4	N

As you can see, every table has an ID column which is what I am joining on, however some tables have not got all 5 (25 in my actual data) of the IDs and some tables have more than one result for each ID. The result I am getting is as follows.

ID	Label_1	Label_2	Label_3
1	A	NULL	NULL
2	B	F	J
2	B	G	J
2	B	F	K
2	B	G	K
2	B	F	L
2	B	G	L
3	C	H	NULL
4	D	NULL	M
4	D	NULL	N
5	E	I	NULL

What I would like to get is 

ID	Label_1	Label_2	Label_3
1	A
2	B	F	J
2		G	K
2			L
3	C	H
4	D		M
4			N
5	E	I

I am happy for the ID column to stay as is or to not have duplicates, just as long as the Label columns are not duplicated. 

Kind Regards,

James

[CharlieS · Accepted solution]

The Tile tool is my favorite tool for use cases like this. Set the Tile tool to "Unique Value" and it works as a GroupBy RecordID tool. Use this to assign a RecordID (Tile_Num) for each unique value that occurs in each "Label_" field, and join on ID and the new RecordID (Tile_Num) field. Finally, a Multi Row Formula tool will fill in the missing ID values. 

Check out the attached solution and let me know if you have any questions. 

[JSL99]

Worked perfectly! 

Thanks a lot!