There are many posts on joining data but i haven't been able to find one for exactly what i am trying to do. My two data sets have a many to many relationship and neither the join nor union can do what i want it to do. See below for an example of the data:
Input 1
| Project ID | Milestone Date | Milestone |
1 | 10/1/20 | Milestone 1 |
| 1 | 10/15/20 | Milestone 2 |
| 1 | 10/31/20 | Milestone 3 |
Input 2
| Project ID | Milestone Date | Milestone |
1 | 10/2/20 | Milestone 1a |
| 1 | 10/14/20 | Milestone 2a |
I need to join on Project ID but that would give me 6 rows of data. It is impossible to add the other columns of data to the join criteria as they are almost never named the same or use the same dates, but i would like to be able to group them together to compare the datasets so it would look like this (sometimes i have more records on the Left and sometimes i have more records on the Right)
Desired Output
| Input 1 Project ID | Input 1 Milestone Date | Input 1 Milestone | Input 2 Project ID | Input 2 Milestone Date | Input 2 Milestone |
1 | 10/1/20 | Milestone 1 | 1 | 10/2/20 | Milestone 1a |
| 1 | 10/15/20 | Milestone 2 | 1 | 10/14/20 | Milestone 2a |
| 1 | 10/31/20 | Milestone 3 | Null | Null | Null |
Any Ideas on how to go about this?
