I have a dataset with ID, names and year. Each ID has multiple records with different names and their corresponding year of joining. I want to transpose data based on ID and the year of joining.
Each row should have unique ID
Name column will be split into Name1, Name2, Name3 and so on
Name1 column should contain date with earliest year of joining followed by Name2 and then Name3
For ID as 1 the name1 column contains John as John has the earliest joining date for ID as 1
Input | |||
ID | Name | Year | |
1 | Steve | 2008 | |
1 | Neha | 2006 | |
1 | John | 2005 | |
2 | Nisha | 2012 | |
2 | Natasha | 2021 | |
2 | Natila | 2000 | |
3 | Richard | 2014 | |
3 | Rishabh | 2013 | |
Output | |||
ID | Name1 | Name2 | Name3 |
1 | John | Neha | Steve |
2 | Natalia | Nisha | Natasha |
3 | Rishabh | Richard |
If there is more than 1 Name for an ID then additional names will be transposed to another filed based on the date of joining.
Much appreciate your help.
Best
Pooja
Solved! Go to Solution.
@Analytics_Pooja
I like to use Tile tool in this case.
Thanks @TheOC .
There are 2 logics that I am still having an issue.
1. IDs are not always in sequence
2. The number of records can be more than 3 for each ID. I also need to increment the column name accordingly like Nmae1, Name2, Name3 and so on..
Best
Pooja
hey @Analytics_Pooja
No problem we can first sort data by dates, and then use a formula to rename the id fields.
Please find attached.
Hope this helps,
TheOC
Thanks @TheOC . This works