Speeding up data entry

[tigerzen]

Looking for a formula based solution if possible, otherwise VBA. Desired format is in rows 7 to 10. I can use TEXTSPLIT to split the cells with the dash separator easily enough but there are quite a number of columns to add headings to and this is what's slowing me down.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Kicks	Clearances	Scores	Tackles
2	Team A	20-15	7-8	10-4	2-4
3	Team B	33-12	4-5	6-5	5-8
4	Team C	14-34	9-13	7-5	3-7
5
6
7		Kicks	Kicks for	Kicks Against	Clearances	Clearances For	Clearances against	Scores	Scores for	Scores against	Tackles	Tackles for	Tackles against
8	Team A	20-15	20	15	7-8	7	8	10-4	10	4	2-4	2	4
9	Team B	33-12	33	12	4-5	4	5	6-5	6	5	5-8	5	8
10	Team C	14-34	14	34	9-13	9	13	7-5	7	5	3-7	3	7
Sheet1

 

[ISY]

Hi

=TEXTSPLIT(TEXTJOIN(" ",TRUE,B2:E2&" "&SUBSTITUTE(B2:E2,"-"," "))," ")

 

[bebo021999]

C8 copy to whole table:

=CHOOSE(MOD(COLUMNS($A:B)-1,3)+1,VLOOKUP($A8,$A$2:$E$4,INT((COLUMNS($A:B)-1)/3)+2,0),LEFT(B8,SEARCH("-",B8)-1),MID(A8,SEARCH("-",A8)+1,10))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Kicks	Clearances	Scores	Tackles
2	Team A	20-15	7-8	10-4	2-4
3	Team B	33-12	4-5	6-5	5-8
4	Team C	14-34	9-13	7-5	3-7
5
6
7		Kicks	Kicks for	Kicks Against	Clearances	Clearances For	Clearances against	Scores	Scores for	Scores against	Tackles	Tackles for	Tackles against
8	Team A	20-15	20	15	7-8	7	8	10-4	10	4	2-4	2	4
9	Team B	33-12	33	12	4-5	4	5	6-5	6	5	5-8	5	8
10	Team C	14-34	14	34	9-13	9	13	7-5	7	5	3-7	3	7
Sheet1

Cell Formulas
Range	Formula
B8:B10	B8	=CHOOSE(MOD(COLUMNS($A:A)-1,3)+1,VLOOKUP($A8,$A$2:$E$4,INT((COLUMNS($A:A)-1)/3)+2,0),LEFT(A8,SEARCH("-",A8)-1),MID(#REF!,SEARCH("-",#REF!)+1,10))
C8:M10	C8	=CHOOSE(MOD(COLUMNS($A:B)-1,3)+1,VLOOKUP($A8,$A$2:$E$4,INT((COLUMNS($A:B)-1)/3)+2,0),LEFT(B8,SEARCH("-",B8)-1),MID(A8,SEARCH("-",A8)+1,10))

 

[Anonymous1378]

Since you have 365, try

=LET(rng,B1:E4,HSTACK(A1:A4&"",SORTBY(HSTACK(rng,TEXTBEFORE(rng,"-",,,,rng&" For"),TEXTAFTER(rng,"-",,,,rng&" Against")),MOD(SEQUENCE(,COLUMNS(rng)*3)-1,4)+1)))

 

[tigerzen]

Thanks for the responses so far, both solutions posted address the separation of data under the main headings but I need something to enter Row 7 which is what is slowing me down.

 

[tigerzen]

Just seen your response Anonymous1378 and it looks like it will work for my real data set, thank you.

 

[Anonymous1378]

Just seen your response Anonymous1378 and it looks like it will work for my real data set, thank you.

One thing needs to change, the 4 should be replaced by COLUMNS(rng)