ollyhughes1982
Well-known Member
- Joined
- Nov 27, 2018
- Messages
- 795
- Office Version
- 365
- Platform
- MacOS
Hi,
In the image (and file - link at the bottom) you can see a worksheet called ‘All Completed Runs - Period’, where I attempt to complete the periodic table of elements, with the event (venue) names of parkruns that I have attended. You can see the criteria / logic for this in A2.
I currently have the following formula in K4, which I have filled down to the bottom of the range (K121):
This works / worked perfectly up until now. However, I am now looking to add an extra complication; to only be able to use each parkrun event (venue) once in the list. I have tried various things, but I’ve been unable to achieve this. Please could anyone advise a way I could achieve this?
When assigning the parkrun event (venue) to the element, I would like to apply the earliest (by date that event was run at for the first time) to the earliest element in the table, from top down to the bottom.
e.g. In my data, Hereford parkrun (attended on 06/07/2019) would be selected for for Hydrogen, but not for Helium, as that is lower down the list. Helium should use Haverford West parkrun, as that was attended the next earliest from my list of parkruns (attended on 08/02/2020).
e.g. 2. In my data, Newport parkrun (attended on 11/04/2011) would be selected for Nitrogen, but not for Neon, as that is lower down the list. Helium should use Newent parkrun, as that was attended the next earliest from my list of parkruns (attended on 02/11/2019).
e.g. 3. In my data, Riverfront parkrun (attended on 21/10/2017) would be selected for Rhenium, but not for Radon, as that is lower down the list. Radon should use Ross-on-Wye parkrun, as that was attended the next earliest from my list of parkruns (attended on 03/08/2019).
Link to small file here: Periodic Table Example.xlsx
Thanks in advance,
Olly.
In the image (and file - link at the bottom) you can see a worksheet called ‘All Completed Runs - Period’, where I attempt to complete the periodic table of elements, with the event (venue) names of parkruns that I have attended. You can see the criteria / logic for this in A2.
I currently have the following formula in K4, which I have filled down to the bottom of the range (K121):
Excel Formula:
=IF(A4<>"",XLOOKUP(IF(LEN(B4)=2,(INDEX(FILTER('All Completed Runs'!$BX$4:$BX$2003,('All Completed Runs'!$AS$4:$AS$2003=MID(B4,1,1))*(ISNUMBER(SEARCH(MID(B4,2,1),'All Completed Runs'!$GP$4:$GP$2003))),""),1)),INDEX(FILTER('All Completed Runs'!$BX$4:$BX$2003,'All Completed Runs'!$AS$4:$AS$2003=(MID(B4,1,1)),""),1)),'All Completed Runs'!$BX$4:$BX$2003,'All Completed Runs'!$C$4:$C$2003),"")This works / worked perfectly up until now. However, I am now looking to add an extra complication; to only be able to use each parkrun event (venue) once in the list. I have tried various things, but I’ve been unable to achieve this. Please could anyone advise a way I could achieve this?
When assigning the parkrun event (venue) to the element, I would like to apply the earliest (by date that event was run at for the first time) to the earliest element in the table, from top down to the bottom.
e.g. In my data, Hereford parkrun (attended on 06/07/2019) would be selected for for Hydrogen, but not for Helium, as that is lower down the list. Helium should use Haverford West parkrun, as that was attended the next earliest from my list of parkruns (attended on 08/02/2020).
e.g. 2. In my data, Newport parkrun (attended on 11/04/2011) would be selected for Nitrogen, but not for Neon, as that is lower down the list. Helium should use Newent parkrun, as that was attended the next earliest from my list of parkruns (attended on 02/11/2019).
e.g. 3. In my data, Riverfront parkrun (attended on 21/10/2017) would be selected for Rhenium, but not for Radon, as that is lower down the list. Radon should use Ross-on-Wye parkrun, as that was attended the next earliest from my list of parkruns (attended on 03/08/2019).
Link to small file here: Periodic Table Example.xlsx
Thanks in advance,
Olly.
