Complex Transpose?

[cschaaf]

I've tried to search, but I'm not sure what keywords will get me the right results.

I have ~1000 data records each with 6 data points. The data is currently arranged like this:

Driver 1	Avg Duration	4
	Avg Repair	10
	Escalation	9
	Score	9
	Surveys	15
	Volume	1000
Driver 2	Avg Duration	88
	Avg Repair	3
	Escalation	8
	Score	10
	Surveys	100
	Volume	5000

I need to transpose those records so it looks like this:

	Avg Duration	Avg Repair	Escalation	Score	Surveys	Volume
Driver 1	4	10	9	9	15	1000
Driver 2	88	3	8	10	100	5000

I'm not sure how to get this to work.

Thanks!

 

[alansidman]

With Power Query aka Get and Transform Data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Column1"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Column2]), "Column2", "Column3")
in
    #"Pivoted Column"

Book12
	A	B	C	D	E	F	G	H	I	J	K
1	Column1	Column2	Column3		Column1	Avg Duration	Avg Repair	Escalation	Score	Surveys	Volume
2	Driver 1	Avg Duration	4		Driver 1	4	10	9	9	15	1000
3		Avg Repair	10		Driver 2	88	3	8	10	100	5000
4		Escalation	9
5		Score	9
6		Surveys	15
7		Volume	1000
8	Driver 2	Avg Duration	88
9		Avg Repair	3
10		Escalation	8
11		Score	10
12		Surveys	100
13		Volume	5000
Sheet1

 

[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’)

 

[cschaaf]

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’)

Sorry! Updated. I'm using O365 on Windows. I'm not an advanced user.

With Power Query aka Get and Transform Data

This looks like what I need. Off to learn about Power Query now! I'll start with the video in your signature. Thank you!

 

[Fluff]

Updated. I'm using O365 on Windows.

Thanks for that.
With a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1							Avg Duration	Avg Repair	Escalation	Score	Surveys	Volume
2	Driver 1	Avg Duration	4			Driver 1	4	10	9	9	15	1000
3		Avg Repair	10			Driver 2	88	3	8	10	100	5000
4		Escalation	9
5		Score	9
6		Surveys	15
7		Volume	1000
8	Driver 2	Avg Duration	88
9		Avg Repair	3
10		Escalation	8
11		Score	10
12		Surveys	100
13		Volume	5000
14
Data

Cell Formulas
Range	Formula
G1:L1	G1	=TOROW(B2:B7)
F2:F3	F2	=TOCOL(A2:A1000,1)
G2:L3	G2	=WRAPROWS(TOCOL(C2:C1000,1),6)
Dynamic array formulas.

 

[HighAndWilder]

I've tried to search, but I'm not sure what keywords will get me the right results.

I have ~1000 data records each with 6 data points. The data is currently arranged like this:

Driver 1	Avg Duration	4
	Avg Repair	10
	Escalation	9
	Score	9
	Surveys	15
	Volume	1000
Driver 2	Avg Duration	88
	Avg Repair	3
	Escalation	8
	Score	10
	Surveys	100
	Volume	5000

I need to transpose those records so it looks like this:

	Avg Duration	Avg Repair	Escalation	Score	Surveys	Volume
Driver 1	4	10	9	9	15	1000
Driver 2	88	3	8	10	100	5000

I'm not sure how to get this to work.

Thanks!

By using formulas.

Complex Transpose.xlsm
	G	H	I	J	K	L	M
3		Avg Duration	Avg Repair	Escalation	Score	Surveys	Volume
4	Driver 1	3419	4438	2371	2988	2509	2320
5	Driver 2	3367	1491	3095	2342	4083	2859
6	Driver 3	3974	1585	893	1114	2999	130
7	Driver 4	1698	4736	1057	2451	1715	4054
8	Driver 5	3671	4798	3162	3544	770	1558
9	Driver 6	3177	1906	2092	3122	3475	663
10	Driver 7	2506	3567	3136	3778	699	4506
11	Driver 8	3168	3491	1892	4134	774	4720
12	Driver 9	727	4804	4849	2743	2502	1200
13	Driver 10	996	1333	4438	1241	1980	598
14	Driver 11	1277	927	4192	1625	2447	2305
15	Driver 12	4039	3848	4630	402	906	1745
16	Driver 13	4761	1113	1573	2951	2963	1705
17	Driver 14	3617	1965	3209	1301	3523	1135
18	Driver 15	4041	1416	2448	2590	1290	3120
19	Driver 16	4172	675	4700	577	196	4155
20	Driver 17	1532	3959	2973	4804	3600	4284
Sheet1

Cell Formulas
Range	Formula
G4:G20	G4	=UNIQUE(FILTER(A1:A102,A1:A102<>""))
H3:M3	H3	=TRANSPOSE(B1:B6)
H4:M20	H4	=TRANSPOSE(OFFSET(INDIRECT("A" & MATCH($G4,$A:$A,0)),0,2,6,1))
Dynamic array formulas.

 

[Dave Patton]

N.B. Fluff posted a formula solution.
What information do you ultimately require?
Consider modifying the structure and then prepare your reports from the "Database style data'.
You could use formulas or other tools.

or use formulas to change the structure

T202311a.xlsm
	A	B	C
1	Driver	Type	Num
2	Driver 1	Avg Duration	4
3	Driver 1	Avg Repair	10
4	Driver 1	Escalation	9
5	Driver 1	Score	9
6	Driver 1	Surveys	15
7	Driver 1	Volume	1000
8	Driver 2	Avg Duration	88
9	Driver 2	Avg Repair	3
10	Driver 2	Escalation	8
11	Driver 2	Score	10
12	Driver 2	Surveys	100
13	Driver 2	Volume	5000
2a

use sum or sumproduct

T202311a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2					Column1	Avg Duration	Avg Repair	Escalation	Score	Surveys	Volume
3					Driver 1	4	10	9	9	15	1000
4					Driver 2	88	3	8	10	100	5000
5
6					Driver 1	4	10	9	9	15	1000
7					Driver 2	88	3	8	10	100	5000
8
2aa

Cell Formulas
Range	Formula
E3:E4,E6:E7	E3	=UNIQUE(Driver)
F3:K4	F3	=SUM(--(Driver=$E6)*(Type=F$2)*Num)
F6:K7	F6	=SUMPRODUCT(--(Driver=$E6),--(Type=F$2),Num)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Driver	='2a'!$A$2:$A$13	F3:K4, F6:K7, E3, E6
Num	='2a'!$C$2:$C$13	F3:K4, F6:K7
rData	='2a'!$A$2:$C$13	F3:K4, F6:K7, E3, E6
Type	='2a'!$B$2:$B$13	F3:K4, F6:K7

 

[cschaaf]

Thanks for that.
With a formula

This worked! Amazing! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.