Index / Match - convert data in rows to data in columns

[HughT]

A colleague has created a table with data headers in alternate rows. I need to convert this to data in separate columns to create a chart. So data is in alternate rows Fred/Dave Fred/Dave Fred/Dave etc. I need to extract the data into a column for Fred and a column for Dave. So in effect a formula to do a copy - paste/transpose. Many thanks.

From this: To This: Fred Dave Fred Dave Fred Dave Fred Dave Fred 7 7 12 15 4 69 47 3 9 Dave 12 Fred 15 Dave 4 Fred 69 Dave 47 Fred 3 Dave 9

 

[Toadstool]

Hi HughT,

You could select cells A3 to B10 then in the formula entry type =TRANSPOSE(C2:J3) using Ctrl-Shift-Enter as it's an array formula.

Book1
	A	B	C	D	E	F	G	H	I	J
1	From this:	To This:
2			Fred	Dave	Fred	Dave	Fred	Dave	Fred	Dave
3	Fred	7	7	12	15	4	69	47	3	9
4	Dave	12
5	Fred	15
6	Dave	4
7	Fred	69
8	Dave	47
9	Fred	3
10	Dave	9
Sheet1

Cell Formulas
Range	Formula
A3:B10	A3	=TRANSPOSE(C2:J3)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[maabadi]

Use this formula at first cell and Press ALT+CTRL+ENTER and then drag it right and down

Book1
	A	B	C	D	E	F
1				Fred	Dave
2	Fred	5		5	12
3	Dave	12		15	19
4	Fred	15		9	15
5	Dave	19		20	20
6	Fred	9		12	12
7	Dave	15		11	5
8	Fred	20
9	Dave	20
10	Fred	12
11	Dave	12
12	Fred	11
13	Dave	5
14
Sheet1

Cell Formulas
Range	Formula
D2:E13	D2	=IFERROR(INDEX($B$2:$B$13,SMALL(IF($A$2:$A$13=D$1,ROW($A$2:$A$13)-ROW(INDEX($A$2:$A$13,1,1))+1),ROWS($D$1:D1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Toadstool]

...or if you don't want to use an array formula:

HughT.xlsx
	A	B	C	D	E	F	G	H	I	J
1	From this:	To This:
2			Fred	Dave	Fred	Dave	Fred	Dave	Fred	Dave
3	Fred	7	7	12	15	4	69	47	3	9
4	Dave	12
5	Fred	15
6	Dave	4
7	Fred	69
8	Dave	47
9	Fred	3
10	Dave	9
Sheet1 (2)

Cell Formulas
Range	Formula
A3:A10	A3	=IFERROR(INDEX($C$2:$ZZ$2,AGGREGATE(15,6,COLUMN($C$2:$ZZ$2)-COLUMN($B$1)/($C$2:$ZZ$2<>""),ROW()-ROW($A$2))),"")
B3:B10	B3	=IFERROR(INDEX($C$3:$ZZ$3,AGGREGATE(15,6,COLUMN($C$2:$ZZ$2)-COLUMN($B$1)/($C$2:$ZZ$2<>""),ROW()-ROW($A$2))),"")

 

[HughT]

Thank you so much! HT

 

[maabadi]

You're Welcome & thanks for feedback.