Need help transposing rows into columns

**mckeven** · 10-08-2014, 02:18 PM

I've searched the net for the life of me, and can't seem to find what I'm looking for. Here's a basic overview of what I'm trying to accomplish:

What I have:
Before.png

What I want:
After.png

I don't know how to word what I'm looking for, but essentially I want to transpose a range of cells, but have the first cell in the column be on the same row as each record.

**Richard Buttrey** · 10-08-2014, 02:33 PM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well. Please don't upload pictures. None of us are inclined to recreate your workbook when you have the original.

**mckeven** · 10-08-2014, 03:02 PM

Here's the attached workbook I took pictures of:
Transpose Help.xlsx

There are 2 worksheets. One is what I have, and the second is what I want.

I'm trying to figure out how to automate this for another workbook I'm working on that has hundreds and hundreds of rows similar to this.

Thanks

**AlKey** · 10-08-2014, 03:18 PM

in G1 and copy formula down

=COUNTA(B1:F1)

in H1 and copy down array entered formula

=IFERROR(INDEX($A$1:$A$4,MATCH(TRUE,MMULT(--(ROW($A$1:$A$4)>=TRANSPOSE(ROW($A$1:$A$4))),$G$1:$G$4)>=ROWS($1:1),0)),"")

in I1 and copy down array entered formula

=IFERROR(INDIRECT(TEXT(SMALL(IF(B$1:F$4<>"",ROW(B$1:F$4)*10^4+COLUMN(B$1:F$4)),ROWS(I$2:I2)),"R0000C0000"),0),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B	C	D	E	F	G	H	I
1	Test 1	A	B	C	D		4	Test 1	A
2	Test 2	A	B				2	Test 1	B
3	Test 3	E	F	G	H	I	5	Test 1	C
4	Test 4	4	5	6			3	Test 1	D
5								Test 2	A
6								Test 2	B
7								Test 3	E
8								Test 3	F
9								Test 3	G
10								Test 3	H
11								Test 3	I
12								Test 4	4
13								Test 4	5
14								Test 4	6