Transform using a formula

[Tbonetone]

Hi all. I've been struggling with this a while. Running around in circles now. Effectively I have flat data populated by a data entry system. So the source has rows added or deleted over time. It is a people record and in particular, it records which programmes a person is allocate too. Each Portfolio and Progamme is related. So in the attached example, a Portfolio 1 entry would relate to Programme 1 entry. Portfolio 2, to Programme 2, and Portfolio 3 to Programme 3. So effectively a person can be on up to three assignments. I need to report these assignments but at the Portfolio and Programme level. I've tried pivoting on it, INDEX and MATCH etc. I confess to coming up to my knowledge level on this...

In the example, I effectively want to transform the rows into columns so I can collate the data into something like this, which I can then sort by Portfolio etc.

Portfolio Programme Last Name
Portfolio A Programme A Jones
Portfolio A Programme B Jones
Portfolio T Programme D Jones
Portfolio A Programme B Wilson
Portfolio C Programme C Doe
Portfolio B Programme B Doe
Portfolio B Programme E Franklin
Portfolio T Programme T Pepper
Portfolio A Programme C Stevens
Portfolio B Programme B Stevens
Portfolio S Programme G Perez
Portfolio A Programme A Smith
Portfolio B Programme B Smith