How to use a pivot table to identify duplicate words in different columns

I am a PE teacher and my pupils need to choose 3 sporting activities to be assessed in as part of their final grade. They have a wide selection of activities. I want to be able to create a pivot table to gather the information across 3 columns and identify all duplicate activities into 1 column, so that it creates a list of pupil who have chosen each activity.

I can do this if I put all information into column 2 and have each pupil name repeated 3 times in column 1. However, this means I must manually copy and paste from the 3 columns.

Any help would gratefully received.

Thanks, Craig

I have tried using lots of different functions but not getting any success in finding the right one.

As you said, you need to change the setup of your data to use pivot table. but good news, you don't need to copy and paste. Here is the formula you can use:

=HSTACK(TOCOL(A2:A29&EXPAND("",1,3,""),0,FALSE),TOCOL(B2:D29,0,FALSE))

I got the idea from this youtube video: https://www.youtube.com/watch?v=Os8Yo3RiAVw