Copying and transposing data from one worksheet to another

[parvezs27]

Hi everyone,

I am an Excel beginner, with basic knowledge.

I am collecting Australian Football Data into Excel.
I've gone through what the process is like below.

I want to know how I can make this process more efficient and automated? Can I utilise a macro or something else that can copy, paste and transpose the data as my final desired result?

Thank you!

1. Copy data from table on website (screenshot below) into Excel.

2. Paste into excel and match destination formatting:

3. Transpose, copy and paste into another worksheet, final desired result below:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
1	COPIED AND PASTED DATA FROM WEBSITE
2
3	Total Disposals	266	247	19			Christ Church Grammar	Guilford Grammar School	Diff			Christ Church Grammar	Guilford Grammar School	Diff
4	Contested Disposals	87	84	3		Total Marks	66	38	28		Hit Outs	55	29	26
5	Uncontested Disposals	179	163	16		Contested Marks	8	2	6		Hit Outs to Advantage	15	8	7
6	Total Kicks	174	159	15		Uncontested Marks	58	36	22		Total Tackles	70	70	0
7	Intercept Possessions	60	47	13		Intercept Marks	14	11	3		Effective Tackles	49	53	-4
8	Effective Kicks	99	87	12		Total Inside 50 Marks	9	10	-1		Back 50 Tackles	9	14	-5
9	Ineffective Kicks	75	72	3		Deep Inside 50 Marks	1	4	-3		Midfield Tackles	49	49	0
10	Total Handballs	92	88	4		Shallow Inside 50 Marks	8	6	2		Forward 50 Tackles	12	7	5
11	Effective Handballs	74	68	6		Total Inside 50's	38	39	-1		Frees Kicks	22	32	-10
12	Ineffective Handballs	18	20	-2		Shallow Inside 50's	26	30	-4		50m Penalty	0	1	-1
13	Handball Receives	8	8	0		Deep Inside 50's	12	9	3		Total Turnovers	60	75	-15
14	Total Clearances	41	40	1		Total Rebound 50's	31	26	5		Back 50 Turnovers	11	6	5
15	Clearances - Center Bounce	9	10	-1		Shallow Rebound 50's	26	18	8		Midfield Turnovers	34	47	-13
16	Clearances - Ball up	19	18	1		Deep Rebound 50's	5	8	-3		Forward 50 Turnovers	15	22	-7
17	Clearances - Throw in	13	12	1		Total Ground Balls	89	107	-18		Goals	11	8	3
18	Pressure Acts	76	77	-1		Loose Ball Gets	68	79	-11		Behinds	8	8	0
19	Knock Ons	8	19	-11		Hard Ball Gets	21	28	-7		Rushed Behinds	0	0	0
20						Fumbles	18	9	9
21	DESIRED FORMAT OF THE DATA
22
23	Team	Round	Win/Loss	Total Disposals	Contested Disposals	Uncontested Disposals	Total Kicks	Intercept Possessions	Effective Kicks	Ineffective Kicks	Total Handballs	Effective Handballs	Ineffective Handballs	Handball Receives	Total Clearances	Clearances - Center Bounce	Clearances - Ball up	Clearances - Throw in	Pressure Acts	Knock Ons	Total Marks	Contested Marks	Uncontested Marks	Intercept Marks	Total Inside 50 Marks	Deep Inside 50 Marks	Shallow Inside 50 Marks	Total Inside 50's	Shallow Inside 50's	Deep Inside 50's	Total Rebound 50's	Shallow Rebound 50's	Deep Rebound 50's	Total Ground Balls	Loose Ball Gets	Hard Ball Gets	Fumbles	Hit Outs	Hit Outs to Advantage	Total Tackles	Effective Tackles	Back 50 Tackles	Midfield Tackles	Forward 50 Tackles	Frees Kicks	50m Penalty	Total Turnovers	Back 50 Turnovers	Midfield Turnovers	Forward 50 Turnovers	Goals	Behinds	Rushed Behinds
24	Wesley College PSA	1	1	296	90	206	172	52	103	69	124	103	21	6	28	8	11	9	56	3	64	7	57	18	11	1	10	38	27	11	43	27	16	91	67	24	9	26	5	58	30	14	33	11	24	0	75	6	48	21	9	6	2
25	Aquinas College	1	0	307	100	207	199	49	106	93	108	82	26	5	31	6	10	15	69	1	74	12	62	12	12	2	10	49	35	14	29	18	11	95	69	26	6	36	18	62	35	7	39	16	26	3	70	5	31	34	7	8	0
26	Guildford Grammar	1	0	255	85	170	153	52	85	68	102	76	26	13	23	6	8	9	108	20	61	5	56	12	7	2	5	30	26	4	38	29	9	101	69	32	11	24	3	63	28	15	36	12	15	1	75	5	49	21	3	4	1
27	Hale School	1	1	328	116	212	201	65	143	58	127	104	23	4	31	10	12	9	67	9	95	19	76	20	19	5	14	53	40	13	27	23	4	117	85	32	4	34	8	39	20	4	26	9	17	0	60	4	31	25	16	12	3
28	Christ Church Grammar	1	1	308	90	218	206	63	129	77	102	80	22	3	29	4	15	10	81	8	86	15	71	14	14	5	9	48	32	16	29	23	6	129	98	31	12	29	3	65	25	6	47	12	23	0	73	4	37	32	11	6	1
29	Trinity College	1	0	312	106	206	205	59	105	100	107	84	23	5	35	8	13	14	109	12	78	14	64	14	6	0	6	34	28	6	37	29	8	120	83	37	10	30	11	59	27	14	38	7	21	1	86	6	64	16	3	7	1
30	Trinity College	2	1	386	139	247	266	81	158	108	120	107	13	6	37	9	11	17	83	11	119	20	99	28	8	0	8	54	48	6	50	44	6	131	98	33	10	35	14	84	77	16	50	18	20	0	93	6	56	31	9	6	1
Sheet1

 

[jdellasala]

I suggest using Power Query to bring the data in from the web page and transforming it in the Power Query editor. It's relatively simple. Great You Tube playlists can be found here and here.