Transpose 3 rows formula only sometimes works

[JeffGrant]

Hi All,

I am transposing from 3 rows to in 1 column to 1 row and 3 columns. This formula is give me hit and miss results. I have colour coded the hits and left the misses blank.

Can somebody please correct the formula and maybe explain why it is doing this.

Thanks

Book1
	A	B	C	D	E	F	G
1					Weights (kg)
2	Name	Weight (kg)		Name	1	2	3
3	Nick Of Time	58.00		Nick Of Time	58.00	58.00	58.00
4	Nick Of Time	58.00		Forty Niner	58.00	58.00	58.00
5	Nick Of Time	58.00		Gratias Deo	0.00	56.50	56.50
6	Forty Niner	58.00		Dorado	58.00	55.50	55.50
7	Forty Niner	58.00		Joyrize	56.00	56.00	56.00
8	Forty Niner	58.00		Lucie Manette	56.00	55.50	55.50
9	Gratias Deo	0.00		Artienne	0.00	56.00	56.00
10	Gratias Deo	58.00		She's A Sweet Deel	0.00	56.00	56.00
11	Gratias Deo	56.50		Miss Rona	56.00	56.00	56.00
12	Dorado	58.00		Wuhan Warrior	0.00	56.00	56.00
13	Dorado	55.00		Forever Autumn	0.00	56.00	56.00
14	Dorado	55.50		Hootie Da Blowfish	0.00	57.00	57.00
15	Joyrize	56.00		Via Monte	0.00	57.00	57.00
16	Joyrize	56.00		The Lady Is A Vamp	55.00	55.00	55.00
17	Joyrize	56.00		State Of Power	54.00	54.00	54.00
18	Lucie Manette	56.00		Augusta Red	54.00	54.00	54.00
19	Lucie Manette	54.00		Tiny'n'tuff	55.00	55.00	55.00
20	Lucie Manette	55.50		Next Stop The Moon	56.00	60.50	60.50
21	Artienne	0.00		Native Chimes	56.00	56.00	56.00
22	Artienne	55.00		Luke's Choice	57.00	58.50	58.50
23	Artienne	56.00		Conquered Zone	52.00	55.00	55.00
24	She's A Sweet Deel	0.00		Costa Lante	52.50	54.00	54.00
25	She's A Sweet Deel	0.00		Flying Target	53.00	52.50	52.50
26	She's A Sweet Deel	56.00		Miss Zedel	56.00	56.00	56.00
27	Miss Rona	56.00		Power And Passion	54.00	51.00	51.00
28	Miss Rona	56.00		Overthought	57.00	57.50	57.50
29	Miss Rona	56.00		Crossaro	58.50	58.50	58.50
30	Wuhan Warrior	0.00		Aragain	59.00	54.00	54.00
31	Wuhan Warrior	56.00
32	Wuhan Warrior	56.00
33	Forever Autumn	0.00
34	Forever Autumn	56.00
35	Forever Autumn	56.00
36	Hootie Da Blowfish	0.00
37	Hootie Da Blowfish	0.00
38	Hootie Da Blowfish	57.00
39	Via Monte	0.00
40	Via Monte	0.00
41	Via Monte	57.00
42	The Lady Is A Vamp	55.00
43	The Lady Is A Vamp	55.00
44	The Lady Is A Vamp	55.00
45	State Of Power	54.00
46	State Of Power	54.50
47	State Of Power	54.00
48	Augusta Red	54.00
49	Augusta Red	54.00
50	Augusta Red	54.00
51	Tiny'n'tuff	55.00
52	Tiny'n'tuff	52.00
53	Tiny'n'tuff	55.00
54	Next Stop The Moon	56.00
55	Next Stop The Moon	56.00
56	Next Stop The Moon	60.50
57	Native Chimes	56.00
58	Native Chimes	55.00
59	Native Chimes	56.00
60	Luke's Choice	57.00
61	Luke's Choice	55.00
62	Luke's Choice	58.50
63	Conquered Zone	52.00
64	Conquered Zone	52.50
65	Conquered Zone	55.00
66	Costa Lante	52.50
67	Costa Lante	53.00
68	Costa Lante	54.00
69	Flying Target	53.00
70	Flying Target	53.00
71	Flying Target	52.50
72	Miss Zedel	56.00
73	Miss Zedel	58.50
74	Miss Zedel	56.00
75	Power And Passion	54.00
76	Power And Passion	54.00
77	Power And Passion	51.00
78	Overthought	57.00
79	Overthought	54.50
80	Overthought	57.50
81	Crossaro	58.50
82	Crossaro	55.50
83	Crossaro	58.50
84	Aragain	59.00
85	Aragain	54.00
86	Aragain	54.00
Sheet1

Cell Formulas
Range	Formula
D3:D30	D3	=UNIQUE($A3:$A86)
E3:E30	E3	=IFERROR(VALUE(INDEX($B$3:$B$2000, SMALL(IF($D3=$A$3:$A$2000, ROW($A$3:$A$2000)-MIN(ROW($A$3:$A$2000))+1, ""), $E$2))),0)
F3:F30	F3	=IFERROR(VALUE(INDEX($B$3:$B$2000, SMALL(IF($D3=$A$2:$A$2000, ROW($A$3:$A$2000)-MIN(ROW($A$3:$A$2000))+1, ""), F$2))),0)
G3:G30	G3	=IFERROR(VALUE(INDEX($B$3:$B$2000, SMALL(IF($D3=$A$3:$A$2000, ROW($A$3:$A$2000)-MIN(ROW($A$3:$A$2000))+1, ""), G$2))),0)
Dynamic array formulas.

 

[Fluff]

You're formula in column F is different to the other 2 columns. It has A$2 not A$3
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1					Weights (kg)
2	Name	Weight (kg)		Name	1	2	3
3	Nick Of Time	58		Nick Of Time	58	58	58
4	Nick Of Time	58		Forty Niner	58	58	58
5	Nick Of Time	58		Gratias Deo	0	58	56.5
6	Forty Niner	58		Dorado	58	55	55.5
7	Forty Niner	58		Joyrize	56	56	56
8	Forty Niner	58		Lucie Manette	56	54	55.5
9	Gratias Deo	0		Artienne	0	55	56
10	Gratias Deo	58		She's A Sweet Deel	0	0	56
11	Gratias Deo	56.5		Miss Rona	56	56	56
12	Dorado	58		Wuhan Warrior	0	56	56
13	Dorado	55		Forever Autumn	0	56	56
14	Dorado	55.5		Hootie Da Blowfish	0	0	57
15	Joyrize	56		Via Monte	0	0	57
16	Joyrize	56		The Lady Is A Vamp	55	55	55
17	Joyrize	56		State Of Power	54	54.5	54
18	Lucie Manette	56		Augusta Red	54	54	54
19	Lucie Manette	54		Tiny'n'tuff	55	52	55
20	Lucie Manette	55.5		Next Stop The Moon	56	56	60.5
21	Artienne	0		Native Chimes	56	55	56
22	Artienne	55		Luke's Choice	57	55	58.5
23	Artienne	56		Conquered Zone	52	52.5	55
24	She's A Sweet Deel	0		Costa Lante	52.5	53	54
25	She's A Sweet Deel	0		Flying Target	53	53	52.5
26	She's A Sweet Deel	56		Miss Zedel	56	58.5	56
27	Miss Rona	56		Power And Passion	54	54	51
28	Miss Rona	56		Overthought	57	54.5	57.5
29	Miss Rona	56		Crossaro	58.5	55.5	58.5
30	Wuhan Warrior	0		Aragain	59	54	54
31	Wuhan Warrior	56
32	Wuhan Warrior	56
33	Forever Autumn	0
34	Forever Autumn	56
35	Forever Autumn	56
36	Hootie Da Blowfish	0
37	Hootie Da Blowfish	0
38	Hootie Da Blowfish	57
39	Via Monte	0
40	Via Monte	0
41	Via Monte	57
42	The Lady Is A Vamp	55
43	The Lady Is A Vamp	55
44	The Lady Is A Vamp	55
45	State Of Power	54
46	State Of Power	54.5
47	State Of Power	54
48	Augusta Red	54
49	Augusta Red	54
50	Augusta Red	54
51	Tiny'n'tuff	55
52	Tiny'n'tuff	52
53	Tiny'n'tuff	55
54	Next Stop The Moon	56
55	Next Stop The Moon	56
56	Next Stop The Moon	60.5
57	Native Chimes	56
58	Native Chimes	55
59	Native Chimes	56
60	Luke's Choice	57
61	Luke's Choice	55
62	Luke's Choice	58.5
63	Conquered Zone	52
64	Conquered Zone	52.5
65	Conquered Zone	55
66	Costa Lante	52.5
67	Costa Lante	53
68	Costa Lante	54
69	Flying Target	53
70	Flying Target	53
71	Flying Target	52.5
72	Miss Zedel	56
73	Miss Zedel	58.5
74	Miss Zedel	56
75	Power And Passion	54
76	Power And Passion	54
77	Power And Passion	51
78	Overthought	57
79	Overthought	54.5
80	Overthought	57.5
81	Crossaro	58.5
82	Crossaro	55.5
83	Crossaro	58.5
84	Aragain	59
85	Aragain	54
86	Aragain	54
List

Cell Formulas
Range	Formula
D3:D30	D3	=UNIQUE($A3:$A86)
E3:G30	E3	=TRANSPOSE(FILTER($B$3:$B$2000,$A$3:$A$2000=D3))
Dynamic array formulas.

 

[Marcelo Branco]

The formula in E3 has an extra (wrong) $
It should be E$2, not $E$2

M.

 

[alansidman]

Pivot your data in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Weight (kg)", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Weight (kg)")
in
    #"Pivoted Column"

 

[JeffGrant]

A HUGE thank you to all.

I have been looking at that for over two hours.

Another case of too close to the trees to see the forest.


AGAIN - many thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JeffGrant]

Pivot your data in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Weight (kg)", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Weight (kg)")
in
    #"Pivoted Column"

Thanks Alan. I am only an absolute novice at PQ and really only started to use it in an absolute basic way earlier today

 

[alansidman]

Steps I took using the U/I
Bring data into PQ Editor
On Add a column, select Add Index. Select start with 1
Next select Standard from the same tab and then select Modulo
Remove the Index Column
On the transform tab, highlight the modulo column and select Pivot
Select Weight to Pivot and click on Advanced and select Do not Aggregate
Close and Load to your Native Excel.

 

[JeffGrant]

Hi All,

In a bid to expand my novice knowledge of PQ, (inspired by Alan Sidman - Thanks), I followed Alan's directions and was getting all very excited, until I found a glitch.

The glitch is this......

The aim is to transpose rows into columns - ok
The data set can have groups of either 1, 2 or 3 records each.
Using the Modulo function, the data happily transfers into columns of three - ok.

The issue is that if here is only 1 or 2 records in a group, then the Mod (3) screws up the transpose.

What needs to happen is that each group must have 3 records before the transpose.

Is there a way that PQ can count the records in each group and:

 

[alansidman]

Look at this link and see if this helps you.

Number rows by group using Power Query

A reader asked if we can number rows by group in Power Query, similar to the PARTITION function in SQL of course there is, we just need a little code.

www.excelguru.ca