Transposing Rows to Columns using the Index function

[bearcub]

I periodically have to upload a file into our commission system where I have to transpose an employee ID in one columns, multiple rows into 1 row multiple columns. I also have to insert 100 between each transposed ID number.

Today, I have 19 people that I have add to the template - 19 rows x 2 columns into 1 row by 38 columns

I have seen this trick on ExcelIsFun but forgot which video it was so I can manipulate the formula. He as several videos on this but I can't remember which one applies to my situation.

Can you transpose something like this using some of the new array functions?

How do I transpose a 19 row, 2 column range to 38 columns (I.d. Employee ID, 100)

Thank you for your help

Michael

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID	Paid	Rows
2	CA11 0SA	Yes	5
3	DY14 0JB	Yes			CA11 0SA	Yes	DY14 0JB	Yes	OL14 6XB	Yes	DL13 1JZ	Yes	NE47 9LQ	Yes
4	OL14 6XB	Yes
5	DL13 1JZ	Yes
6	NE47 9LQ	Yes
7
8
Main

Cell Formulas
Range	Formula
C2	C2	=COUNTA(A:A)-1
E3:N3	E3	=INDEX(A2:B100,INT(SEQUENCE(,C2*2,1,0.5)),MOD(SEQUENCE(,C2*2,2),2)+1)
Dynamic array formulas.

 

[alansidman]

an alternative available to you is Power Query also called Get & Transform and found on the Data Tab of your Excel Version

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source) 
in
    #"Transposed Table"

Using Fluff's example, here is an alternative presentation.

Book3
	A	B	C	D	E
1	Column1	Column2	Column3	Column4	Column5
2	CA11 0SA	DY14 0JB	OL14 6XB	DL13 1JZ	NE47 9LQ
3	Yes	Yes	Yes	Yes	Yes
Sheet2

 

[Fluff]

If your just pasting data into col A, you can automatically add100 to col B like

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Paid	Rows
2	CA11 0SA	100	5
3	DY14 0JB	100			CA11 0SA	100	DY14 0JB	100	OL14 6XB	100	DL13 1JZ	100	NE47 9LQ	100
4	OL14 6XB	100
5	DL13 1JZ	100
6	NE47 9LQ	100
7
Main

Cell Formulas
Range	Formula
B2:B6	B2	=SEQUENCE(C2,,100,0)
C2	C2	=COUNTA(A:A)-1
E3:N3	E3	=INDEX(A2:B100,INT(SEQUENCE(,C2*2,1,0.5)),MOD(SEQUENCE(,C2*2,2),2)+1)
Dynamic array formulas.