Transpose data to Horizantal

[sekhar03]

This is related to Fantasy cricket. I'm trying to build an excel which will simplify preparing Grand League teams. Am facing issues in transposing data to horizantal.

Source Data:

IPL_PlayBook_Temp.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	SNO		Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7	Team 8	Team 9	Team 10	Team 11
3	1	Dinesh Karthik (C	1	1	1	1	1	1	1	1	1	1	1	11
4	2	Quinton de Kock (wk)	1	1	1	1	1	1	1	1	1	1	1	11
5	3	Ishan Kishan	1	1	1	1	1	1	1	1	1	1	1	11
6	4	Rahul Tripathi	1					1		1		1		4
7	5	Shubman Gill		1		1	1				1	1	1	6
8	6	Nitish Rana			1			1	1	1	1			5
9	7	Eoin Morgan	1	1		1	1		1			1	1	7
10	8	Rohit Sharma (c)	1							1	1			3
11	9	Suryakumar Yadav		1	1	1	1	1	1				1	7
12	10	Andre Russell	1						1	1		1	1	5
13	11	Kieron Pollard			1	1					1	1		4
14	12	Hardik Pandya			1		1			1			1	4
15	13	Krunal Pandya		1				1	1			1		4
16	14	C Green	1		1						1		1	4
17	15	Pat Cummins		1					1					2
18	16	Kamlesh Nagarkoti			1			1						2
19	17	P Krishna	1	1		1	1	1						5
20	18	Varun Chakravarthy					1		1	1	1			4
21	19	J Pattinson			1	1	1					1	1	5
22	20	Rahul Chahar		1	1						1			3
23	21	Trent Boult	1	1		1		1	1	1		1	1	8
24	22	Jasprit Bumrah	1			1	1	1		1	1			6
25	23		11	11	11	11	11	11	11	11	11	11	11
Sheet2

Cell Formulas
Range	Formula
N3:N24	N3	=COUNTIF(C3:M3,1)
C25:M25	C25	=COUNTIF(C3:C24,"1")

My Target data should look like:

IPL_PlayBook_Temp.xlsm
	P	Q	R	S	T	U	V	W	X	Y	Z	AA
29	Player1	Player2	Player3	Player4	Player5	Player6	Player7	Player8	Player9	Player10	Player11
30	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Nitish Rana	Rohit Sharma (c)	Eoin Morgan	Andre Russell	J Pattinson	Trent Boult	P Krishna	L Ferguson
31	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Nitish Rana	Rohit Sharma (c)	Rahul Tripathi	Kieron Pollard	J Pattinson	Trent Boult	P Krishna	Varun Chakravarthy
32	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Nitish Rana	Rohit Sharma (c)	Shubman Gill	Krunal Pandya	J Pattinson	Trent Boult	P Krishna	Pat Cummins
33	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Nitish Rana	Eoin Morgan	Hardik Pandya	Andre Russell	J Pattinson	P Krishna	L Ferguson	Varun Chakravarthy
34	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Nitish Rana	Eoin Morgan	Rahul Tripathi	Andre Russell	J Pattinson	P Krishna	Varun Chakravarthy	Pat Cummins
35	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Nitish Rana	Shubman Gill	Hardik Pandya	Andre Russell	Trent Boult	P Krishna	L Ferguson	Varun Chakravarthy
36	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Rohit Sharma (c)	Eoin Morgan	Suryakumar Yadav	Andre Russell	Trent Boult	L Ferguson	Pat Cummins	Jasprit Bumrah
37	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Rohit Sharma (c)	Rahul Tripathi	Shubman Gill	Kieron Pollard	P Krishna	L Ferguson	Pat Cummins	Jasprit Bumrah
38	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Eoin Morgan	Hardik Pandya	Suryakumar Yadav	Krunal Pandya	P Krishna	L Ferguson	Pat Cummins	Rahul Chahar
39	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Rahul Tripathi	Shubman Gill	Nitish Rana	Andre Russell	Varun Chakravarthy	L Ferguson	Pat Cummins	Jasprit Bumrah
40	Quinton de Kock (wk)	Dinesh Karthik (C	Ishan Kishan	Rahul Tripathi	Suryakumar Yadav	Nitish Rana	Andre Russell	L Ferguson	Trent Boult	Varun Chakravarthy	Rahul Chahar
Sheet2

 

[pedro-egoli]

This is a simple transpose done by copy and Paste special/Tramspose

SNO	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
	Dinesh Karthik (C	Quinton de Kock (wk)	Ishan Kishan	Rahul Tripathi	Shubman Gill	Nitish Rana	Eoin Morgan	Rohit Sharma (c)	Suryakumar Yadav	Andre Russell	Kieron Pollard	Hardik Pandya	Krunal Pandya	C Green	Pat Cummins	Kamlesh Nagarkoti	P Krishna	Varun Chakravarthy	J Pattinson	Rahul Chahar	Trent Boult	Jasprit Bumrah
Team 1	1	1	1	1			1	1		1				1			1				1	1	11
Team 2	1	1	1		1		1		1				1		1		1			1	1		11
Team 3	1	1	1			1			1		1	1		1		1			1	1			11
Team 4	1	1	1		1		1		1		1						1		1		1	1	11
Team 5	1	1	1		1		1		1			1					1	1	1			1	11
Team 6	1	1	1	1		1			1				1			1	1				1	1	11
Team 7	1	1	1			1	1		1	1			1		1			1			1		11
Team 8	1	1	1	1		1		1		1		1						1			1	1	11
Team 9	1	1	1		1	1		1			1			1				1		1		1	11
Team 10	1	1	1	1	1		1			1	1		1						1		1		11
Team 11	1	1	1		1		1		1	1		1		1					1		1		11
	11	11	11	4	6	5	7	3	7	5	4	4	4	4	2	2	5	4	5	3	8	6

Not sure if this was what you were after

 

[sekhar03]

This is a simple transpose done by copy and Paste special/Tramspose

SNO	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
	Dinesh Karthik (C	Quinton de Kock (wk)	Ishan Kishan	Rahul Tripathi	Shubman Gill	Nitish Rana	Eoin Morgan	Rohit Sharma (c)	Suryakumar Yadav	Andre Russell	Kieron Pollard	Hardik Pandya	Krunal Pandya	C Green	Pat Cummins	Kamlesh Nagarkoti	P Krishna	Varun Chakravarthy	J Pattinson	Rahul Chahar	Trent Boult	Jasprit Bumrah
Team 1	1	1	1	1			1	1		1				1			1				1	1	11
Team 2	1	1	1		1		1		1				1		1		1			1	1		11
Team 3	1	1	1			1			1		1	1		1		1			1	1			11
Team 4	1	1	1		1		1		1		1						1		1		1	1	11
Team 5	1	1	1		1		1		1			1					1	1	1			1	11
Team 6	1	1	1	1		1			1				1			1	1				1	1	11
Team 7	1	1	1			1	1		1	1			1		1			1			1		11
Team 8	1	1	1	1		1		1		1		1						1			1	1	11
Team 9	1	1	1		1	1		1			1			1				1		1		1	11
Team 10	1	1	1	1	1		1			1	1		1						1		1		11
Team 11	1	1	1		1		1		1	1		1		1					1		1		11
	11	11	11	4	6	5	7	3	7	5	4	4	4	4	2	2	5	4	5	3	8	6

Not sure if this was what you were after

Simple Transpose doesn't help here.
We need to arrive to "Target Data" from "source data" which mentioned in the problem.