Display Header at next Column

Dear Friends,

I received extracted data where the arrangements seems difficult to get the end result. The Headers are located on top and I'm supposed to do some sort of filtering so that the Header will appear on the left side. From what i know, to filter out such result, the values have to somehow be side by side but now the Headers are on top.

I have attached a file to display the intended end result. Since the data is sensitive, i have to substitute them with football data. I've tried using basic filtering, where i can extract out those under the category England by creating a working cell column beside denoting "England". Only thing is how to display the Team names beside the Players names?

Thanks for your help in advance.

Hi Sixthsense,

I'm doing a simulation to show that the end result wants to filter out values from row 14-16 while it also wants the Team to be displayed at the side.

Hi AlKey.

Thank you very much for your help. The formula looks quite difficult to understand but it does help to solve my case.