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]

Then how is a formula going to know?

Surely that's what your current formula does.

Yes, but why doesn't it pick up 4 as it should then? How would the apps be able to achieve it lime they do? Almost like it needs to loop over

 

[Fluff]

Because there is nothing to pick for Fm as Frogmary Green Farm parkrun had already been used.

 

[ollyhughes1982]

Because there is nothing to pick for Fm as Frogmary Green Farm parkrun had already been used.

Yes, that’s the issue. Is there no way to do it so that it looks at the events as a whole and can then allocate them correctly? Seems like it would have to be a macro or something, unfortunately

 

[ollyhughes1982]

Because there is nothing to pick for Fm as Frogmary Green Farm parkrun had already been used.

I’ll ask the developer how he does it in the code, but not sure if it will help with Excel or not

 

[ollyhughes1982]

These are from the developer. It doesn’t look good!

 

[Fluff]

Without knowing the exact criteria, I cannot help.

 

[ollyhughes1982]

Without knowing the exact criteria, I cannot help.

The only criteria I have is what I've put on before:

The event short name must start with the first letter of the symbol
The second letter (if there is one) must be included anywhere in the short name after the first letter
Events can't be included twice.

I'm thinking there might be a way with helper columns or something, not sure.

 

[ollyhughes1982]

This is the original starting point and logic (image below)

 

[Fluff]

But what is the "best possible combination"?

 

[ollyhughes1982]

But what is the "best possible combination"?

I don’t know. If it was me, where there were multiple events satisfying the criteria, just go for the one completed on an earlier date