Transpose Question

[kazbear]

Hello!
I am using the desktop version of Excel as part of the 365 bundle, all up to date on Windows 10.

My goal is to create a template I can use for adding closed captions to a video, and in this case, I want my final output to be an .srt file, so the format has to be specific. Like this:

1

00:00:00,000 --> 00:00:05,000

Hola, soy Tania. Bienvenida a la Clínica de Planificación

2

00:00:05,000 --> 00:00:10,000

Familiar de la calle Las Flores Sur. Me alegro de

My template has rows that have been set for 5-second intervals between captions (Again, a template to get started), and everything is combined into three cells. Then I transpose those cells to give me the format you see above.
So the part of the sheet I am transposing looks like this:

1​	00:00:00,000 --> 00:00:05,000	Hola, soy Tania. Bienvenida a la Clínica de Planificación
2​	00:00:05,000 --> 00:00:10,000	Familiar de la calle Las Flores Sur. Me alegro de
3​	00:00:10,000 --> 00:00:15,000	que esté aquí para observar la intervención “De mujer
4​	00:00:15,000 --> 00:00:20,000	a mujer: toma el control de tu salud” en acción. Este

and I am using =TRANSPOSE(H3:J3) to give me the format that I need.
My hope was that I could grab the new transpose cells, and drag them down to auto-fill, but of course, it skips to every 4th row.

1

00:00:00,000 --> 00:00:05,000

Hola, soy Tania. Bienvenida a la Clínica de Planificación

5

00:00:20,000 --> 00:00:25,000

curso será útil para las proveedoras que trabajan

9

00:00:40,000 --> 00:00:45,000

de tu salud” o “De Mujer a Mujer”, como la llamaremos

Looking for any direction someone might have to make sure it grabs each row.

 

[JamesCanale]

If you table is starting in A2:

=TOCOL(HSTACK(A2:C5,CHOOSE(SEQUENCE(ROWS(A2:C5),1,1,0),"")))

This is assuming you want a space between each set. If not, it's smaller. You can also drop the last space at the end if you need that.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jeffrey Mahoney]

I like JamesCanale version

If you don't have 365 and your data is in A2:C5
=INDEX($A$2:$C$5,CEILING((ROW()-1)/3,1),ROW()-1-(CEILING((ROW()-1)/3,1)*3-3))

 

[Fluff]

Another option if you have 365 with the latest functions

Fluff.xlsm
	A	B						H	I	J
1
2	1							1	00:00:00,000 --> 00:00:05,000	Hola, soy Tania. Bienvenida a la Clínica de Planificación
3	00:00:00,000 --> 00:00:05,000							2	00:00:05,000 --> 00:00:10,000	Familiar de la calle Las Flores Sur. Me alegro de
4	Hola, soy Tania. Bienvenida a la Clínica de Planificación							3	00:00:10,000 --> 00:00:15,000	que esté aquí para observar la intervención “De mujer
5								4	00:00:15,000 --> 00:00:20,000	a mujer: toma el control de tu salud” en acción. Este
6	2
7	00:00:05,000 --> 00:00:10,000
8	Familiar de la calle Las Flores Sur. Me alegro de
9
10	3
11	00:00:10,000 --> 00:00:15,000
12	que esté aquí para observar la intervención “De mujer
13
14	4
15	00:00:15,000 --> 00:00:20,000
16	a mujer: toma el control de tu salud” en acción. Este
17
18
Temp

Cell Formulas
Range	Formula
A2:A16	A2	=DROP(TOCOL(EXPAND(H2:J5,,4,"")),-1)
Dynamic array formulas.

 

[kazbear]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you! Updated.
(Though I did start my post with my version and platform...)

 

[kazbear]

If you table is starting in A2:

=TOCOL(HSTACK(A2:C5,CHOOSE(SEQUENCE(ROWS(A2:C5),1,1,0),"")))

This is assuming you want a space between each set. If not, it's smaller. You can also drop the last space at the end if you need that.

Yes, this is a great solution. Looks like I have a new function to learn about.
Thank you!

 

[kazbear]

Another option if you have 365 with the latest functions

Fluff.xlsm
	A	B						H	I	J
1
2	1							1	00:00:00,000 --> 00:00:05,000	Hola, soy Tania. Bienvenida a la Clínica de Planificación
3	00:00:00,000 --> 00:00:05,000							2	00:00:05,000 --> 00:00:10,000	Familiar de la calle Las Flores Sur. Me alegro de
4	Hola, soy Tania. Bienvenida a la Clínica de Planificación							3	00:00:10,000 --> 00:00:15,000	que esté aquí para observar la intervención “De mujer
5								4	00:00:15,000 --> 00:00:20,000	a mujer: toma el control de tu salud” en acción. Este
6	2
7	00:00:05,000 --> 00:00:10,000
8	Familiar de la calle Las Flores Sur. Me alegro de
9
10	3
11	00:00:10,000 --> 00:00:15,000
12	que esté aquí para observar la intervención “De mujer
13
14	4
15	00:00:15,000 --> 00:00:20,000
16	a mujer: toma el control de tu salud” en acción. Este
17
18
Temp

Cell Formulas
Range	Formula
A2:A16	A2	=DROP(TOCOL(EXPAND(H2:J5,,4,"")),-1)
Dynamic array formulas.

Thank you, I'm going to try this as well.

 

[Fluff]

Glad we could help & thanks for the feedback.