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.

 

[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:

If count =1, insert two rows after the counted row, copying the name into the two blank cells.
If count = 2, insert one row after the counted row, copying the name into the blank cell.
If count = 3, move to next group.

I have been able to achieve this objective by running a very slow and tedious VBA For loop, starting at the bottom of the data and comparing each row with the one before it.

So I am drinking a lot of coffee whilst this part executes, because there are thousands of records everyday

Any and all ideas are graciously received.

Jeff.

Name	Weight
Double Happy	58.5
Double Happy	53
Double Happy	54.5
Twin Spinner	63
Twin Spinner	55
Charred	54
Charred	63.5
Charred	65.5
La Casa	56.5
La Casa	58
La Casa	67
Timberlake	57.5
Timberlake	58.5
Timberlake	57.5
Maestro Blu	65.5
Maestro Blu	59.5
O'Susana	55.5
O'Susana	55.5
O'Susana	56
Elle D'Berry	54
Elle D'Berry	66.5
Elle D'Berry	55
Tunzagutz	54
Tunzagutz	54
Tunzagutz	54.5
Renegade Fighter	54
Renegade Fighter	51
Zenntari	66
Zenntari	56
Zenntari	56
Poacher	54.5
Poacher	56.5
Poacher	57.5
Maitland	55.5
Brookbourne	57.5
Brookbourne	57.5
Brookbourne	57.5
Youknowhatimean	55.5
Youknowhatimean	55.5
Youknowhatimean	58.5
The Twinkling	56.5
The Twinkling	53.5
The Twinkling	57.5
Beauty For Ashes	56.5
Beauty For Ashes	54.5
Beauty For Ashes	54
Kiki	56.5
Kiki	56.5
Kiki	57.5
Glad All Over	53.5
Glad All Over	56.5
Glad All Over	54.5
Bouffant	57.5
Bouffant	57.5
Bouffant	57.5
C'est Une Star	55
C'est Une Star	56.5
C'est Une Star	56.5
Donnstar	64
Jenny Kraig	55.5
Jenny Kraig	56.5
Jenny Kraig	56.5
Lofty	58.5
Lofty	54.5
Lofty	56.5
Captain Oumuamua	57.5
Shockova	66
Shockova	68
Shockova	55.5
Shadow Man	58.5
Beauty For Ashes	56.5
Beauty For Ashes	54.5
Beauty For Ashes	54
Apatchee Power	56.5
Apatchee Power	52.5
Apatchee Power	56.5
Bang Bang	56.5
C'est Une Star	55
C'est Une Star	56.5
C'est Une Star	56.5
Cinnamon Star	56.5
Jenny Kraig	55.5
Jenny Kraig	56.5
Jenny Kraig	56.5
Stormy's Secret	56.5
Stormy's Secret	56.5
Stormy's Secret	56.5
True North	55.5
Half Of Nothing	54.5
Half Of Nothing	54.5
Half Of Nothing	54.5
Perfectionist	56.5
Perfectionist	55
Perfectionist	54.5
She's Dashing	57.5
She's Dashing	55.5
She's Dashing	56.5
Bendermerry	55.5
Bendermerry	54.5

 

[JeffGrant]

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

Hi Alan, is there another way you can send the link? it is giving me a 403 access denied error.

 

[alansidman]

It is just a web address: Try to google Excel Guru Blog and then search within the blog for "Number rows by group using Power Query" If you are at work, maybe your IT department is blocking your access. I was just on the site and had no issues.

 

[johnnyL]

403 sounds like it has been blocked by a firewall. You may want to look here <--- for workarounds.

 

[JGordon11]

Not the most elegant PQ coding but seems to work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Name1", each _, type table [Name=nullable text, Weight=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Weight1", each [Name1]{0}),
    #"Expanded Weight1" = Table.ExpandRecordColumn(#"Added Custom", "Weight1", {"Weight"}, {"Weight 1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Weight1", "Weight2", each [Name1]{1}),
    #"Expanded Weight2" = Table.ExpandRecordColumn(#"Added Custom1", "Weight2", {"Weight"}, {"Weight 2"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Weight2", "Weight3", each [Name1]{2}),
    #"Expanded Weight3" = Table.ExpandRecordColumn(#"Added Custom2", "Weight3", {"Weight"}, {"Weight 3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Weight3",{"Name1"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Removed Columns", {{"Weight 2", null}, {"Weight 3", null}})
in
    #"Replaced Errors"

AltSort.xlsm
	A	B	C	D	E	F	G	H
1	Name	Weight			Name	Weight 1	Weight 2	Weight 3
2	Double Happy	58.5			Double Happy	58.5	53	54.5
3	Double Happy	53			Twin Spinner	63	55
4	Double Happy	54.5			Charred	54	63.5	65.5
5	Twin Spinner	63			La Casa	56.5	58	67
6	Twin Spinner	55			Timberlake	57.5	58.5	57.5
7	Charred	54			Maestro Blu	65.5	59.5
8	Charred	63.5			O'Susana	55.5	55.5	56
9	Charred	65.5			Elle D'Berry	54	66.5	55
10	La Casa	56.5			Tunzagutz	54	54	54.5
11	La Casa	58			Renegade Fighter	54	51
12	La Casa	67			Zenntari	66	56	56
13	Timberlake	57.5			Poacher	54.5	56.5	57.5
14	Timberlake	58.5			Maitland	55.5
15	Timberlake	57.5			Brookbourne	57.5	57.5	57.5
16	Maestro Blu	65.5			Youknowhatimean	55.5	55.5	58.5
17	Maestro Blu	59.5			The Twinkling	56.5	53.5	57.5
18	O'Susana	55.5			Beauty For Ashes	56.5	54.5	54
19	O'Susana	55.5			Kiki	56.5	56.5	57.5
20	O'Susana	56			Glad All Over	53.5	56.5	54.5
21	Elle D'Berry	54			Bouffant	57.5	57.5	57.5
22	Elle D'Berry	66.5			C'est Une Star	55	56.5	56.5
23	Elle D'Berry	55			Donnstar	64
24	Tunzagutz	54			Jenny Kraig	55.5	56.5	56.5
25	Tunzagutz	54			Lofty	58.5	54.5	56.5
26	Tunzagutz	54.5			Captain Oumuamua	57.5
27	Renegade Fighter	54			Shockova	66	68	55.5
28	Renegade Fighter	51			Shadow Man	58.5
29	Zenntari	66			Apatchee Power	56.5	52.5	56.5
30	Zenntari	56			Bang Bang	56.5
31	Zenntari	56			Cinnamon Star	56.5
32	Poacher	54.5			Stormy's Secret	56.5	56.5	56.5
33	Poacher	56.5			True North	55.5
34	Poacher	57.5			Half Of Nothing	54.5	54.5	54.5
35	Maitland	55.5			Perfectionist	56.5	55	54.5
36	Brookbourne	57.5			She's Dashing	57.5	55.5	56.5
37	Brookbourne	57.5			Bendermerry	55.5	54.5
38	Brookbourne	57.5
39	Youknowhatimean	55.5
40	Youknowhatimean	55.5
41	Youknowhatimean	58.5
42	The Twinkling	56.5
43	The Twinkling	53.5
44	The Twinkling	57.5
45	Beauty For Ashes	56.5
46	Beauty For Ashes	54.5
47	Beauty For Ashes	54
48	Kiki	56.5
49	Kiki	56.5
50	Kiki	57.5
51	Glad All Over	53.5
52	Glad All Over	56.5
53	Glad All Over	54.5
54	Bouffant	57.5
55	Bouffant	57.5
56	Bouffant	57.5
57	C'est Une Star	55
58	C'est Une Star	56.5
59	C'est Une Star	56.5
60	Donnstar	64
61	Jenny Kraig	55.5
62	Jenny Kraig	56.5
63	Jenny Kraig	56.5
64	Lofty	58.5
65	Lofty	54.5
66	Lofty	56.5
67	Captain Oumuamua	57.5
68	Shockova	66
69	Shockova	68
70	Shockova	55.5
71	Shadow Man	58.5
72	Beauty For Ashes	56.5
73	Beauty For Ashes	54.5
74	Beauty For Ashes	54
75	Apatchee Power	56.5
76	Apatchee Power	52.5
77	Apatchee Power	56.5
78	Bang Bang	56.5
79	C'est Une Star	55
80	C'est Une Star	56.5
81	C'est Une Star	56.5
82	Cinnamon Star	56.5
83	Jenny Kraig	55.5
84	Jenny Kraig	56.5
85	Jenny Kraig	56.5
86	Stormy's Secret	56.5
87	Stormy's Secret	56.5
88	Stormy's Secret	56.5
89	True North	55.5
90	Half Of Nothing	54.5
91	Half Of Nothing	54.5
92	Half Of Nothing	54.5
93	Perfectionist	56.5
94	Perfectionist	55
95	Perfectionist	54.5
96	She's Dashing	57.5
97	She's Dashing	55.5
98	She's Dashing	56.5
99	Bendermerry	55.5
100	Bendermerry	54.5
101
Sheet4

 

[alansidman]

Since you can't see the link I provided, I have made an attempt : Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Weight (kg)", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Data", each _, type table [Name=nullable text, #"Weight (kg)"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Weight (kg)", "Index"}, {"Custom.Weight (kg)", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Weight (kg)"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Weight1"}, {"2", "Weight2"}, {"3", "Weight3"}})
in
    #"Renamed Columns"

The key line from the unviewable web sit is the adding of a custom index column which needs to be written in Mcode and not part of the UI.

Table.AddIndexColumn([Data],"Index",1,1)

 

[JeffGrant]

Since you can't see the link I provided, I have made an attempt : Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Weight (kg)", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Data", each _, type table [Name=nullable text, #"Weight (kg)"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Weight (kg)", "Index"}, {"Custom.Weight (kg)", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Weight (kg)"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Weight1"}, {"2", "Weight2"}, {"3", "Weight3"}})
in
    #"Renamed Columns"

The key line from the unviewable web sit is the adding of a custom index column which needs to be written in Mcode and not part of the UI.

Table.AddIndexColumn([Data],"Index",1,1)

Thank you Mr Sidman. I have work commitments for a few days, so I will try your suggestions at the weekend. Much appreciated.

 

[JeffGrant]

Hi All, Just following on from this thread earlier in the week,

Pls: note I do have a similar thread :

PQ - Only return records where count of record is 3 or 6

Hi All, A different question, and the last for the weekend. With the attached sheet, how do I use PQ to return only those records who have a count 3 or 6? Thanks Again J.

www.excelforum.com

Rather than add rows to end up with a total 3 or 6 records per name, I could go the other way and remove rows where the name count is not 3 or 6.

My question is:

How can I uses PQ to filter records where the name count is 3 or 6 before doing the transpose?

The steps Alan provided earlier are perfect for the transpose and will work quite happily if the name count is 3 or 6.

Thanks in advance

PQ3 Test.xlsx
	A	B
1	horse name	form weight
2	A Big Chance	53.5
3	A Big Chance	53
4	A Big Chance	56
5	A Fighting Fury	61.5
6	A Fighting Fury	61
7	A Fighting Fury	61.5
8	A Knight In Paris	58
9	A Knight In Paris	54
10	A Knight In Paris	58
11	A Krupt Pick	58.5
12	A Krupt Pick	55
13	A Krupt Pick	55
14	A Lone Hero	58
15	A Lone Hero	60
16	A Lone Hero	58
17	A Lone Hero	58
18	A Lone Hero	60
19	A Lone Hero	58
20	A Magic Zariz	60
21	A Magic Zariz	63.5
22	A Magic Zariz	58.5
23	A Midnight Shadow	55.5
24	A Pinch Of Luck	56
25	A Pinch Of Luck	59.5
26	A Pinch Of Luck	60
27	A Tender Lady	58
28	A Tender Lady	57
29	A Tender Lady	59
30	A Thousand Degrees	54
31	A Thousand Degrees	59.5
32	A Thousand Degrees	55
33	Aadelad	54
34	Aadelad	57
35	Aadelad	54
36	Abb Roy	56.5
37	Abb Roy	55.5
38	Abb Roy	55
39	Abbey Bridge	55
40	Abdicating	57.5
41	Abdicating	58.5
42	Abdicating	54
43	Abeldane	53
44	Abeldane	51.5
45	Abeldane	54
46	Aberdeen Queen	60.5
47	Aberdeen Queen	54.5
48	Aberdeen Queen	57.5
49	Aberlady	59
50	Aberlady	55.5
51	Aberlady	55.5
52	Aberlady	57.5
53	Aberlady	59
54	Aberlady	55.5
55	Ablestock	66.5
56	Ablestock	66.5
57	Ablestock	69.5
58	Above And Beyond	54
59	Above And Beyond	58.5
60	Above And Beyond	59
61	Above The Sky	57
62	Above The Sky	54
63	Above The Sky	57.5
64	Absconded	56.5
65	Absconded	56
66	Absconded	56.5
67	Absolvido	52.5
68	Absolvido	53.5
69	Absolvido	54.5
70	Accelerato	57.5
71	Accelerato	59.5
72	Accelerato	55.5
73	Accelerato	57.5
74	Accelerato	59.5
75	Accelerato	55.5
76	Accordingly	53
77	Accordingly	57
78	Accordingly	56
79	Accountability	58.5
80	Accountability	58.5
81	Accountability	57
82	Accountability	58.5
83	Accountability	58.5
84	Accountability	57
85	Accoy	55
86	Accoy	56.5
87	Accoy	55
88	Accumulated	57
89	Accumulated	59.5
90	Accumulated	57.5
91	Acrobatic	54.5
92	Acrobatic	55
93	Acrobatic	57
94	Adabeyaat	60.5
95	Adabeyaat	56.5
96	Adabeyaat	55.5
97	Adamas Prince	57
98	Adamas Prince	57
99	Adamas Prince	54
100	Adatto	58.5
Sheet1

 

[JeffGrant]

Thank you Mr Sidman. I have work commitments for a few days, so I will try your suggestions at the weekend. Much appreciated.

Hi Alan,

I have tried copy/paste your query above and all I get is errors, I just don't know enough about PQ yet to understand what this is doing. I have tried editing table & Column names and thought everything else would line up, but alas, it didn't.

 

[JeffGrant]

Not the most elegant PQ coding but seems to work

[QUOTE="JGordon11, post: 5724749, member: 471747"]
Not the most elegant PQ coding but seems to work

[CODE=pq]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Name1", each _, type table [Name=nullable text, Weight=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Weight1", each [Name1]{0}),
    #"Expanded Weight1" = Table.ExpandRecordColumn(#"Added Custom", "Weight1", {"Weight"}, {"Weight 1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Weight1", "Weight2", each [Name1]{1}),
    #"Expanded Weight2" = Table.ExpandRecordColumn(#"Added Custom1", "Weight2", {"Weight"}, {"Weight 2"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Weight2", "Weight3", each [Name1]{2}),
    #"Expanded Weight3" = Table.ExpandRecordColumn(#"Added Custom2", "Weight3", {"Weight"}, {"Weight 3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Weight3",{"Name1"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Removed Columns", {{"Weight 2", null}, {"Weight 3", null}})
in
    #"Replaced Errors"

AltSort.xlsm
	A	B	C	D	E	F	G	H
1	Name	Weight			Name	Weight 1	Weight 2	Weight 3
2	Double Happy	58.5			Double Happy	58.5	53	54.5
3	Double Happy	53			Twin Spinner	63	55
4	Double Happy	54.5			Charred	54	63.5	65.5
5	Twin Spinner	63			La Casa	56.5	58	67
6	Twin Spinner	55			Timberlake	57.5	58.5	57.5
7	Charred	54			Maestro Blu	65.5	59.5
8	Charred	63.5			O'Susana	55.5	55.5	56
9	Charred	65.5			Elle D'Berry	54	66.5	55
10	La Casa	56.5			Tunzagutz	54	54	54.5
11	La Casa	58			Renegade Fighter	54	51
12	La Casa	67			Zenntari	66	56	56
13	Timberlake	57.5			Poacher	54.5	56.5	57.5
14	Timberlake	58.5			Maitland	55.5
15	Timberlake	57.5			Brookbourne	57.5	57.5	57.5
16	Maestro Blu	65.5			Youknowhatimean	55.5	55.5	58.5
17	Maestro Blu	59.5			The Twinkling	56.5	53.5	57.5
18	O'Susana	55.5			Beauty For Ashes	56.5	54.5	54
19	O'Susana	55.5			Kiki	56.5	56.5	57.5
20	O'Susana	56			Glad All Over	53.5	56.5	54.5
21	Elle D'Berry	54			Bouffant	57.5	57.5	57.5
22	Elle D'Berry	66.5			C'est Une Star	55	56.5	56.5
23	Elle D'Berry	55			Donnstar	64
24	Tunzagutz	54			Jenny Kraig	55.5	56.5	56.5
25	Tunzagutz	54			Lofty	58.5	54.5	56.5
26	Tunzagutz	54.5			Captain Oumuamua	57.5
27	Renegade Fighter	54			Shockova	66	68	55.5
28	Renegade Fighter	51			Shadow Man	58.5
29	Zenntari	66			Apatchee Power	56.5	52.5	56.5
30	Zenntari	56			Bang Bang	56.5
31	Zenntari	56			Cinnamon Star	56.5
32	Poacher	54.5			Stormy's Secret	56.5	56.5	56.5
33	Poacher	56.5			True North	55.5
34	Poacher	57.5			Half Of Nothing	54.5	54.5	54.5
35	Maitland	55.5			Perfectionist	56.5	55	54.5
36	Brookbourne	57.5			She's Dashing	57.5	55.5	56.5
37	Brookbourne	57.5			Bendermerry	55.5	54.5
38	Brookbourne	57.5
39	Youknowhatimean	55.5
40	Youknowhatimean	55.5
41	Youknowhatimean	58.5
42	The Twinkling	56.5
43	The Twinkling	53.5
44	The Twinkling	57.5
45	Beauty For Ashes	56.5
46	Beauty For Ashes	54.5
47	Beauty For Ashes	54
48	Kiki	56.5
49	Kiki	56.5
50	Kiki	57.5
51	Glad All Over	53.5
52	Glad All Over	56.5
53	Glad All Over	54.5
54	Bouffant	57.5
55	Bouffant	57.5
56	Bouffant	57.5
57	C'est Une Star	55
58	C'est Une Star	56.5
59	C'est Une Star	56.5
60	Donnstar	64
61	Jenny Kraig	55.5
62	Jenny Kraig	56.5
63	Jenny Kraig	56.5
64	Lofty	58.5
65	Lofty	54.5
66	Lofty	56.5
67	Captain Oumuamua	57.5
68	Shockova	66
69	Shockova	68
70	Shockova	55.5
71	Shadow Man	58.5
72	Beauty For Ashes	56.5
73	Beauty For Ashes	54.5
74	Beauty For Ashes	54
75	Apatchee Power	56.5
76	Apatchee Power	52.5
77	Apatchee Power	56.5
78	Bang Bang	56.5
79	C'est Une Star	55
80	C'est Une Star	56.5
81	C'est Une Star	56.5
82	Cinnamon Star	56.5
83	Jenny Kraig	55.5
84	Jenny Kraig	56.5
85	Jenny Kraig	56.5
86	Stormy's Secret	56.5
87	Stormy's Secret	56.5
88	Stormy's Secret	56.5
89	True North	55.5
90	Half Of Nothing	54.5
91	Half Of Nothing	54.5
92	Half Of Nothing	54.5
93	Perfectionist	56.5
94	Perfectionist	55
95	Perfectionist	54.5
96	She's Dashing	57.5
97	She's Dashing	55.5
98	She's Dashing	56.5
99	Bendermerry	55.5
100	Bendermerry	54.5
101
Sheet4

[/CODE]

AltSort.xlsm
	A	B	C	D	E	F	G	H
1	Name	Weight			Name	Weight 1	Weight 2	Weight 3
2	Double Happy	58.5			Double Happy	58.5	53	54.5
3	Double Happy	53			Twin Spinner	63	55
4	Double Happy	54.5			Charred	54	63.5	65.5
5	Twin Spinner	63			La Casa	56.5	58	67
6	Twin Spinner	55			Timberlake	57.5	58.5	57.5
7	Charred	54			Maestro Blu	65.5	59.5
8	Charred	63.5			O'Susana	55.5	55.5	56
9	Charred	65.5			Elle D'Berry	54	66.5	55
10	La Casa	56.5			Tunzagutz	54	54	54.5
11	La Casa	58			Renegade Fighter	54	51
12	La Casa	67			Zenntari	66	56	56
13	Timberlake	57.5			Poacher	54.5	56.5	57.5
14	Timberlake	58.5			Maitland	55.5
15	Timberlake	57.5			Brookbourne	57.5	57.5	57.5
16	Maestro Blu	65.5			Youknowhatimean	55.5	55.5	58.5
17	Maestro Blu	59.5			The Twinkling	56.5	53.5	57.5
18	O'Susana	55.5			Beauty For Ashes	56.5	54.5	54
19	O'Susana	55.5			Kiki	56.5	56.5	57.5
20	O'Susana	56			Glad All Over	53.5	56.5	54.5
21	Elle D'Berry	54			Bouffant	57.5	57.5	57.5
22	Elle D'Berry	66.5			C'est Une Star	55	56.5	56.5
23	Elle D'Berry	55			Donnstar	64
24	Tunzagutz	54			Jenny Kraig	55.5	56.5	56.5
25	Tunzagutz	54			Lofty	58.5	54.5	56.5
26	Tunzagutz	54.5			Captain Oumuamua	57.5
27	Renegade Fighter	54			Shockova	66	68	55.5
28	Renegade Fighter	51			Shadow Man	58.5
29	Zenntari	66			Apatchee Power	56.5	52.5	56.5
30	Zenntari	56			Bang Bang	56.5
31	Zenntari	56			Cinnamon Star	56.5
32	Poacher	54.5			Stormy's Secret	56.5	56.5	56.5
33	Poacher	56.5			True North	55.5
34	Poacher	57.5			Half Of Nothing	54.5	54.5	54.5
35	Maitland	55.5			Perfectionist	56.5	55	54.5
36	Brookbourne	57.5			She's Dashing	57.5	55.5	56.5
37	Brookbourne	57.5			Bendermerry	55.5	54.5
38	Brookbourne	57.5
39	Youknowhatimean	55.5
40	Youknowhatimean	55.5
41	Youknowhatimean	58.5
42	The Twinkling	56.5
43	The Twinkling	53.5
44	The Twinkling	57.5
45	Beauty For Ashes	56.5
46	Beauty For Ashes	54.5
47	Beauty For Ashes	54
48	Kiki	56.5
49	Kiki	56.5
50	Kiki	57.5
51	Glad All Over	53.5
52	Glad All Over	56.5
53	Glad All Over	54.5
54	Bouffant	57.5
55	Bouffant	57.5
56	Bouffant	57.5
57	C'est Une Star	55
58	C'est Une Star	56.5
59	C'est Une Star	56.5
60	Donnstar	64
61	Jenny Kraig	55.5
62	Jenny Kraig	56.5
63	Jenny Kraig	56.5
64	Lofty	58.5
65	Lofty	54.5
66	Lofty	56.5
67	Captain Oumuamua	57.5
68	Shockova	66
69	Shockova	68
70	Shockova	55.5
71	Shadow Man	58.5
72	Beauty For Ashes	56.5
73	Beauty For Ashes	54.5
74	Beauty For Ashes	54
75	Apatchee Power	56.5
76	Apatchee Power	52.5
77	Apatchee Power	56.5
78	Bang Bang	56.5
79	C'est Une Star	55
80	C'est Une Star	56.5
81	C'est Une Star	56.5
82	Cinnamon Star	56.5
83	Jenny Kraig	55.5
84	Jenny Kraig	56.5
85	Jenny Kraig	56.5
86	Stormy's Secret	56.5
87	Stormy's Secret	56.5
88	Stormy's Secret	56.5
89	True North	55.5
90	Half Of Nothing	54.5
91	Half Of Nothing	54.5
92	Half Of Nothing	54.5
93	Perfectionist	56.5
94	Perfectionist	55
95	Perfectionist	54.5
96	She's Dashing	57.5
97	She's Dashing	55.5
98	She's Dashing	56.5
99	Bendermerry	55.5
100	Bendermerry	54.5
101
Sheet4

[/QUOTE]

Hi JGordon11,

I figured out where to paste the code -> The Advance Editor , but when I paste it and make sure that the Table number is the same, I only get null figures for the weights,

Any ideas?