Limit filter list to unique values

[ollyhughes1982]

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

=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.

 

[ollyhughes1982]

Thanks, @Fluff. I have just tried this and seems bit better, as in Hereford's second entry has now been replaced with Haverfordwest, but am still getting duplicates. I have attched a link to the version where I have added your formula in column O. Periodic Table Example_v2.xlsx Yes, this also works perfectly. Thanks very much, @Fluff. Excellent, as always.

 

[smozgur]

Yes, this also works perfectly. Thanks very much, @Fluff. Excellent, as always.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.