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.

 

[JGordon11]

Can you send image of PQ user interface at each step In Applied Steps. Like below

 

[alansidman]

Jeff, Here is my file of your data with the PQ solution.

PQ-Jeff Grant.xlsx | Powered by Box

app.box.com

 

[JeffGrant]

Jeff, Here is my file of your data with the PQ solution.

PQ-Jeff Grant.xlsx | Powered by Box

app.box.com

Good Morning Mr Sidman,

thank you for that, this solution is perfect, when there are 3 records (or multiples of three records) per name. (which is why the MOD function you proposed earlier would have worked very well).

Because the step after this transpose is to run a Linear Regression with 3 data points, the number of records IN the this data set needs to be 3 per name, otherwise the transpose gets all out of synch with the use of an excel formula. However, if we can get this Transpose to work properly in PQ, the Excel transpose formula will be removed.

Unfortunately, that data coming into the model from the data supplier can have any number between 1 & 30 records per Name. I eliminated as much as I can before getting to this transpose procedure.

The issue is transposing records where there are only 1 or 2 records per Name.

JGordon above has also put up a solution, but I cant make it work. All I get is Null in the Weights columns.

I have tried several ways (and many many many hours) to solve this issue via VBA to insert/delete the rows until the count is 3 (or a multiple of 3, because 6 records per name is also acceptable as being relevant data), or using PQ to delete names where the row count is less than, or not a multiple of 3.

Just to give you an understanding of why this part is critical. In the passed 5 days alone, there were over 20,000 records to be analized.

My VBA works fine when there are hundreds of records, but when we get into the thousands, it does not cope.

If we can make this transpose work properly in PQ, then the VBA code and Excel Transpose formula will no longer be required and I can remove them all together.

Thus moving straight into the linear regression.

This is a very critical and extremely important step......in the whole process

Really hoping that we can find a solution.

Cheers

 

[JeffGrant]

Can you send image of PQ user interface at each step In Applied Steps. Like below

View attachment 43391

 

[JeffGrant]

Good Morning Mr Gordon, I hope the uploaded images will allow you to spot the issue.

Cheers & Thanks

 

[JeffGrant]

A huge Thank you to Alan and J.

I understand that this was not an easy one.

I ended up get a solution that works as intended vis Microsoft.

Hers is the link if you would like to review.

Excel PQ to Transpose from Rows to columns

Hi, this is a problem that has cost a lot of time and I don't seem to find an answer on any number of forums/message boards etc. The problem is this... When I bring a data set that looks like this

answers.microsoft.com

Thanks again.