Rows to columns based on id and date

[Analytics_Pooja]

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 

[TheOC]

Hi @Analytics_Pooja 

I believe i have created what you need. 

Please find attached.

Cheers,
TheOC

[Qiu]

@Analytics_Pooja 
I like to use Tile tool in this case.

[Analytics_Pooja]

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

[TheOC · Accepted solution]

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

[Analytics_Pooja]

Thanks @TheOC . This works