Converting vertical data to horizontal data with multiple columns

[Criterium]

I would be grateful for help please in reformatting a vertical list of names, with accompanying data in two further columns, into a list where each named person has one row but multiple columns of data.

In using a pivot table I can achieve the correct layout but the actual alpha data is turned into a count value instead of showing the original alpha character.

The attached example shows before and after. Any help would be much appreciated.

[DonkeyOte]

In general these are generally best resolved using helpers to keep things efficient...

Using your sample file

D5: =$A5&":"&$B5
copied down

At which point, once you set your headers in row 20 such that they match the source values (ie Subject1 rather than Subject 1) then:

B21: =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX($C$5:$C$14,MATCH($A21&":"&B$20,$D$5:$D$14,0))))
copied across matrix

You could as implied do without helpers, you could also achieve same output using VBA - you don't really specify preference(s).

On an aside you can reduce no. of calcs based on the max 5 subject rule along the lines of:

B21: =IF(COUNTIF($A21:A21,"*?")=6,"",LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX($C$5:$C$14,MATCH($A21&":"&B$20,$D$5:$D$14,0)))))
copied across matrix

[MickG]

Hi, Try returned file , Sheet (2) has 2 Codes, Both do the same thing , Results start "E1" sheet (2).
Regards Mick