JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 519
- Office Version
- 365
- Platform
- Windows
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
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. |