I had this formula in a spreadsheet where I was kindly helped from here. I run a running club and I am trying to improve my spreadsheet.
The formula I had was =IFERROR(INDEX(Runners!$P$2:$P$3843,AGGREGATE(15,6,(ROW(Runners!$P$2:$P$3843)-ROW(!$B$2)+1)/(Runners!$A$2:$A$3843='Race 1'!$B339), COLUMNS($O339:O339))),"")
Please ignore the tab names, it is the formula I am struggling with. The formula above gives me the answer 56. Which is correct, no problem with that, but I am trying to add in text of " - " and also another column (their finishing position). So the full answer would be 56 - 1 etc.
I have copied some dummy data below: The data covers the range A1:P30, with the headers being on Row 1. This tab is called Data.
On the tab next to it, named Summary. I have put headings such as Name, Conditions, Course, Distance and Track. Next to those headings I would type the persons naem, the conditions, today's course, the distance and the track.
This would then bring back the most recent results (most recent date first) and then their finishing position. The columns I am trying to capture are 'Rating' and 'Position'. In the example below. I have typed in Turner, Rain, Birmingham, 10k and Slow.
It then looks at the last 5 occurrences of these conditions and brings back the Rating and the Position, results below 85 - 3 87 - 7 75 -1 58 - 9 76 - 4 for the last time "Turner" ran in Conditions that were "Rain" plus a - (with a space either side of the -) then their finishing position. In the screen dump below the merge formatting hasn't worked. There should be 5 cells each for Condition, Course, Distance and Track. I have tried using the xl2bb add in, I hope this has worked.
Any help greatly appreciated.
The formula I had was =IFERROR(INDEX(Runners!$P$2:$P$3843,AGGREGATE(15,6,(ROW(Runners!$P$2:$P$3843)-ROW(!$B$2)+1)/(Runners!$A$2:$A$3843='Race 1'!$B339), COLUMNS($O339:O339))),"")
Please ignore the tab names, it is the formula I am struggling with. The formula above gives me the answer 56. Which is correct, no problem with that, but I am trying to add in text of " - " and also another column (their finishing position). So the full answer would be 56 - 1 etc.
I have copied some dummy data below: The data covers the range A1:P30, with the headers being on Row 1. This tab is called Data.
Name | Number | Position | Total | Date | Conditions | Quick | Course | Distance | Attendance | Track | Classifications | Race Type | Age Range | Paid | Rating |
Turner | 55 | 5 | 25 | 05/12/2019 | Cold | Yes | Birmingham | 10k | Zero | Quick | Mixed | Mixed | Any | Y | 81 |
Turner | 64 | 4 | 25 | 18/11/2019 | Windy | Yes | Manchester | 8k | Zero | Slow | Mixed | Mixed | Any | Y | 82 |
Davis | 33 | 7 | 25 | 18/11/2019 | Windy | Yes | Manchester | 8k | Zero | Slow | Mixed | Mixed | Any | Y | 65 |
Turner | 62 | 3 | 25 | 07/11/2019 | Rain | Yes | Edinburgh | 10k | Zero | Slow | Men | Men | Any | Y | 85 |
Davis | 59 | 2 | 25 | 07/11/2019 | Rain | Yes | Edinburgh | 10k | Zero | Slow | Men | Men | Any | Y | 88 |
Turner | 61 | 7 | 25 | 16/10/2019 | Rain | No | Birmingham | 5k | Zero | Slow | Men | Men | Any | Y | 87 |
Turner | 67 | 1 | 25 | 19/09/2019 | Sunny | Yes | Exeter | 10k | Zero | Medium | Men | Men | Any | Y | 74 |
Davis | 69 | 2 | 25 | 19/09/2019 | Sunny | Yes | Exeter | 10k | Zero | Medium | Men | Men | Any | Y | 71 |
Turner | 62 | 4 | 25 | 10/09/2019 | Sunny | Yes | Birmingham | 8k | Zero | Good | Mixed | Mixed | Any | Y | 52 |
Turner | 71 | 1 | 30 | 26/08/2019 | Rain | Yes | Manchester | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 75 |
Davis | 98 | 2 | 30 | 26/08/2019 | Rain | Yes | Manchester | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 65 |
Turner | 53 | 6 | 40 | 06/08/2019 | Windy | Yes | London | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 53 |
Turner | 58 | 6 | 40 | 24/07/2019 | Hot | Yes | Cardiff | 10k | Zero | Good | Mixed | Mixed | Any | Y | 55 |
Turner | 57 | 9 | 40 | 17/06/2019 | Rain | Yes | Cardiff | 5k | Zero | Slow | Men | Men | Any | Y | 58 |
Turner | 43 | 0 | 40 | 10/06/2019 | Hot | Yes | Manchester | 10k | Zero | Quick | Men | Men | Any | Y | 58 |
Davis | 48 | 5 | 40 | 10/06/2019 | Hot | Yes | Manchester | 10k | Zero | Quick | Men | Men | Any | Y | 68 |
Turner | 64 | 4 | 40 | 28/05/2019 | Rain | Yes | London | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 76 |
Davis | 75 | 2 | 45 | 28/05/2019 | Rain | Yes | London | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 92 |
Turner | 59 | 6 | 40 | 11/05/2019 | Windy | Yes | Birmingham | 8k | Zero | Slow | Mixed | Mixed | Any | Y | 60 |
Turner | 55 | 4 | 40 | 26/11/2018 | Cold | Yes | Manchester | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 70 |
Turner | 36 | 4 | 40 | 08/11/2018 | Cold | Yes | Edinburgh | 6f | Zero | Slow | Mixed | Mixed | Any | Y | 70 |
Davis | 25 | 3 | 40 | 08/11/2018 | Cold | Yes | Edinburgh | 6f | Zero | Slow | Mixed | Mixed | Any | Y | 85 |
Turner | 59 | 2 | 40 | 04/11/2018 | Cold | No | Edinburgh | 5f | Zero | Slow | Mixed | Mixed | Any | Y | 70 |
Turner | 40 | 6 | 40 | 24/08/2018 | Sunny | No | Birmingham | 10k | Zero | Quick | Men | Men | Any | Y | 71 |
Davis | 55 | 2 | 11 | 24/08/2018 | Sunny | No | Birmingham | 10k | Zero | Quick | Men | Men | Any | Y | 84 |
Turner | 61 | 2 | 50 | 11/08/2018 | Hot | Yes | Cardiff | 8k | Zero | Quick | Men | Men | U-25 | Y | 65 |
Davis | 12 | 9 | 46 | 11/08/2018 | Hot | Yes | Cardiff | 8k | Zero | Quick | Men | Men | U-25 | Y | 33 |
Turner | 44 | 7 | 50 | 30/07/2018 | Hot | No | Birmingham | 10k | Zero | Quick | Men | Men | U-25 | Y | 62 |
Turner | 50 | 4 | 50 | 26/05/2018 | Rain | No | Manchester | 5k | Zero | Slow | Men | Men | U-25 | Y | 64 |
On the tab next to it, named Summary. I have put headings such as Name, Conditions, Course, Distance and Track. Next to those headings I would type the persons naem, the conditions, today's course, the distance and the track.
This would then bring back the most recent results (most recent date first) and then their finishing position. The columns I am trying to capture are 'Rating' and 'Position'. In the example below. I have typed in Turner, Rain, Birmingham, 10k and Slow.
It then looks at the last 5 occurrences of these conditions and brings back the Rating and the Position, results below 85 - 3 87 - 7 75 -1 58 - 9 76 - 4 for the last time "Turner" ran in Conditions that were "Rain" plus a - (with a space either side of the -) then their finishing position. In the screen dump below the merge formatting hasn't worked. There should be 5 cells each for Condition, Course, Distance and Track. I have tried using the xl2bb add in, I hope this has worked.
Any help greatly appreciated.
Name | Turner | Conditions | Rain | Course | Birmingham | Distance | 10k | Track | Slow | ||||||||||
Condition | Course | Distance | Track | ||||||||||||||||
85 - 3 | 87 - 7 | 75 - 1 | 58 - 9 | 76 - 4 | 81 - 5 | 87 - 7 | 52 - 4 | 60 - 6 | 71 - 6 | 81 - 5 | 85 - 3 | 74 - 1 | 75 - 1 | 53 - 6 | 82 - 4 | 85 - 3 | 87 - 7 | 75 - 1 | 53 - 6 |
Runner Data.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | Name | Number | Position | Total | Date | Conditions | Quick | Course | Distance | Attendance | Track | Classifications | Race Type | Age Range | Paid | Rating | ||
2 | Turner | 55 | 5 | 25 | 05/12/2019 | Cold | Yes | Birmingham | 10k | Zero | Quick | Mixed | Mixed | Any | Y | 81 | ||
3 | Turner | 64 | 4 | 25 | 18/11/2019 | Windy | Yes | Manchester | 8k | Zero | Slow | Mixed | Mixed | Any | Y | 82 | ||
4 | Davis | 33 | 7 | 25 | 18/11/2019 | Windy | Yes | Manchester | 8k | Zero | Slow | Mixed | Mixed | Any | Y | 65 | ||
5 | Turner | 62 | 3 | 25 | 07/11/2019 | Rain | Yes | Edinburgh | 10k | Zero | Slow | Men | Men | Any | Y | 85 | ||
6 | Davis | 59 | 2 | 25 | 07/11/2019 | Rain | Yes | Edinburgh | 10k | Zero | Slow | Men | Men | Any | Y | 88 | ||
7 | Turner | 61 | 7 | 25 | 16/10/2019 | Rain | No | Birmingham | 5k | Zero | Slow | Men | Men | Any | Y | 87 | ||
8 | Turner | 67 | 1 | 25 | 19/09/2019 | Sunny | Yes | Exeter | 10k | Zero | Medium | Men | Men | Any | Y | 74 | ||
9 | Davis | 69 | 2 | 25 | 19/09/2019 | Sunny | Yes | Exeter | 10k | Zero | Medium | Men | Men | Any | Y | 71 | ||
10 | Turner | 62 | 4 | 25 | 10/09/2019 | Sunny | Yes | Birmingham | 8k | Zero | Good | Mixed | Mixed | Any | Y | 52 | ||
11 | Turner | 71 | 1 | 30 | 26/08/2019 | Rain | Yes | Manchester | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 75 | ||
12 | Davis | 98 | 2 | 30 | 26/08/2019 | Rain | Yes | Manchester | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 65 | ||
13 | Turner | 53 | 6 | 40 | 06/08/2019 | Windy | Yes | London | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 53 | ||
14 | Turner | 58 | 6 | 40 | 24/07/2019 | Hot | Yes | Cardiff | 10k | Zero | Good | Mixed | Mixed | Any | Y | 55 | ||
15 | Turner | 57 | 9 | 40 | 17/06/2019 | Rain | Yes | Cardiff | 5k | Zero | Slow | Men | Men | Any | Y | 58 | ||
16 | Turner | 43 | 0 | 40 | 10/06/2019 | Hot | Yes | Manchester | 10k | Zero | Quick | Men | Men | Any | Y | 58 | ||
17 | Davis | 48 | 5 | 40 | 10/06/2019 | Hot | Yes | Manchester | 10k | Zero | Quick | Men | Men | Any | Y | 68 | ||
18 | Turner | 64 | 4 | 40 | 28/05/2019 | Rain | Yes | London | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 76 | ||
19 | Davis | 75 | 2 | 45 | 28/05/2019 | Rain | Yes | London | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 92 | ||
20 | Turner | 59 | 6 | 40 | 11/05/2019 | Windy | Yes | Birmingham | 8k | Zero | Slow | Mixed | Mixed | Any | Y | 60 | ||
21 | Turner | 55 | 4 | 40 | 26/11/2018 | Cold | Yes | Manchester | 10k | Zero | Slow | Mixed | Mixed | Any | Y | 70 | ||
22 | Turner | 36 | 4 | 40 | 08/11/2018 | Cold | Yes | Edinburgh | 6f | Zero | Slow | Mixed | Mixed | Any | Y | 70 | ||
23 | Davis | 25 | 3 | 40 | 08/11/2018 | Cold | Yes | Edinburgh | 6f | Zero | Slow | Mixed | Mixed | Any | Y | 85 | ||
24 | Turner | 59 | 2 | 40 | 04/11/2018 | Cold | No | Edinburgh | 5f | Zero | Slow | Mixed | Mixed | Any | Y | 70 | ||
25 | Turner | 40 | 6 | 40 | 24/08/2018 | Sunny | No | Birmingham | 10k | Zero | Quick | Men | Men | Any | Y | 71 | ||
26 | Davis | 55 | 2 | 11 | 24/08/2018 | Sunny | No | Birmingham | 10k | Zero | Quick | Men | Men | Any | Y | 84 | ||
27 | Turner | 61 | 2 | 50 | 11/08/2018 | Hot | Yes | Cardiff | 8k | Zero | Quick | Men | Men | U-25 | Y | 65 | ||
28 | Davis | 12 | 9 | 46 | 11/08/2018 | Hot | Yes | Cardiff | 8k | Zero | Quick | Men | Men | U-25 | Y | 33 | ||
29 | Turner | 44 | 7 | 50 | 30/07/2018 | Hot | No | Birmingham | 10k | Zero | Quick | Men | Men | U-25 | Y | 62 | ||
30 | Turner | 50 | 4 | 50 | 26/05/2018 | Rain | No | Manchester | 5k | Zero | Slow | Men | Men | U-25 | Y | 64 | ||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B6 | B6 | =B24 |
Runner Data.xlsx | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
1 | |||||||||||||||||||||||
2 | |||||||||||||||||||||||
3 | Name | Turner | Conditions | Rain | Course | Birmingham | Distance | 10k | Track | Slow | |||||||||||||
4 | |||||||||||||||||||||||
5 | |||||||||||||||||||||||
6 | |||||||||||||||||||||||
7 | Condition | Course | Distance | Track | |||||||||||||||||||
8 | 85 - 3 | 87 - 7 | 75 - 1 | 58 - 9 | 76 - 4 | 81 - 5 | 87 - 7 | 52 - 4 | 60 - 6 | 71 - 6 | 81 - 5 | 85 - 3 | 74 - 1 | 75 - 1 | 53 - 6 | 82 - 4 | 85 - 3 | 87 - 7 | 75 - 1 | 53 - 6 | |||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B8:U8 | B8 | =B13&" - "&B14 |