Converting rows to columns based on cell content

[TimovieMan]

Hi guys,

I have a long list of rows that needs to be converted based on one column's content. The list looks a bit like this:

Player | ID |  Game   | Score | Comment
-----------------------------------------
Anne   | 21 | Javelin | 41.2m | Drizzle, 16km/h headwind
Anne   | 21 | Javelin | 38.1m | Drizzle, 12km/h headwind
Anne   | 21 | Hurdle  | 18.7s | 12 km/h backwind
Anne   | 21 | HJump   | 1m54  | Drizzle
Bob    | 24 | Javelin | 55.1m | -
Bob    | 24 | Hurdle  | 15.4s | 10 km/h headwind
Bob    | 24 | HJump   | 1m72  | -
Bob    | 24 | HJump   | 1m76  | Winner
Colin  | 10 | Hurdle  | 13.0s | 10 km/h headwind, winner

Since there's only a limited number of games (I only gave three in the example but there are five), I want to cut back on the number of rows by dividing that into columns, so I get something like this:

Player | ID | Javelin Score | Javelin comment   | Hurdle score | Hurdle comment    | HJump score | HJump comment
-------------------------------------------------------------------------------------------------------------------
Anne   | 21 | 41.2m | Drizzle, 16 km/h headwind | 18.7s | 12 km/h backwind         | 1m54 | Drizzle
Anne   | 21 | 38.1m | Drizzle, 12 km/h headwind |       |                          |      |
Bob    | 24 | 55.1m | -                         | 15.4s | 10 km/h headwind         | 1m72 | -
Bob    | 24 |       |                           |       |                          | 1m76 | Winner
Colin  | 10 |       |                           | 13.0s | 10 km/h headwind, winner |      |

If there were only one possible option for each row, I could do this with VLOOKUP, but since several are possible and some might be blank, I have no idea how to approach this.

A pivot table got me close to what I needed, but it doesn't show the actual scores and comment, just the number of how many there are, so I'm guessing I'm going to need VBA to do this...
Anyone know an alternative way to do this (maybe with VLOOKUP or a pivot table workaround?), or can you help me out with the VBA code? My own VBA knowledge is pretty much limited to recording macro's...

 

[TimovieMan]

This will probably sound silly and an insane workaround, but I managed to get the result I needed.

I first split all the data in separate worksheets (one for each game).
Then I used a pivot table on the original sheet to calculate the max number of scores and comments I would get for any one player, and created a list with just the players and their IDs, with as many duplicates as I would need in total.
Then I used an array formula I found here: https://www.mrexcel.com/forum/excel...up-return-2nd-matching-value.html#post3074838 (thanks, T. Valko!) to get all the n-th values I would need from each game worksheet.
And finally I deleted all the "#NUM!" values that were returned when no more data was available.

Massive workaround, perhaps, but it got the job done.