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.

 

[ollyhughes1982]

Hi again. I have now re-opened this thread, as it is no longer working correctly as I previously had thought.

The current formula in column K (below) is giving me incorrect results:

=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)))*(ISNA(XMATCH('All Completed Runs'!$C$4:$C$2003,K$3:K3))),""),1)),INDEX(FILTER('All Completed Runs'!$BX$4:$BX$2003,('All Completed Runs'!$AS$4:$AS$2003=(MID(B4,1,1)))*(ISNA(XMATCH('All Completed Runs'!$C$4:$C$2003,K$3:K3))),""),1)),'All Completed Runs'!$BX$4:$BX$2003,'All Completed Runs'!$C$4:$C$2003),"")

It is giving me a total of 100, whereas the online web app says it should be 105 and the Running Achievements phone app also gives me the same 105 result. I have included a link (below) to a copy of the worksheet. I have manually inputted, in columns N and O (headed in yellow), what the correct results (from the web and phone apps) should be. A lot of the results match, but quite a few don’t. I have highlighted in green, the cases where results match (i.e. are correct).

File is here:

https://1drv.ms/x/c/f08b781118912fd2/EcvYqvcX8ftCj_S-fxwXWrYBvlUYBSGlLLY33y2_18Qa7A?e=E1dMEF

I have also included a copy of the online app’s result in its graphical form.

Thanks in advance!

Olly.

 

[Fluff]

You will need to explain exactly why the results are wrong.

 

[ollyhughes1982]

You will need to explain exactly why the results are wrong.

Hi, @Fluff.

For example: For elements beginning with ‘F’, the result should be 4 events (venues) out of the 5 in total for the challenge. However, my current results only give 3 events (venues) out of 5.

3 of them match with the manual results (Five Arches parkrun, Forest of Dean parkrun & Frogmary Green Farm parkrun [highlighted in green]), but Fountains Abbey parkrun is missing [highlighted in red].

This is incorrect, as I have (in total) completed 4 events (venues) beginning with F and between them the 4 do cover each of the F, Fe, Fr & Fm elements, without duplication.

I haven’t yet done either a 5th ‘F’ event (venue) in total, or one that satisfies the second ‘l’ part of the ‘Fl’ required for Flerovium, so that’s correct to be empty (in both cases).

Hope that helps, I know it is a complicated one.

Thanks again, for all of your help.

Olly.

 

[Fluff]

That does not explain why the results you are getting are wrong.

 

[ollyhughes1982]

That does not explain why the results you are getting are wrong.

I should be getting 4 results and I'm getting 3. Not sure how else I can explain it? You can see that the 4 events in column K do meet the criteria for 4 of the 5 element symbols in column B. In column K I am only getting 3 populated, it's missing Fountains Abbey.

 

[Fluff]

But why should F be Fountains Abbey parkrun, rather than Forest of Dean parkrun? That goes for the other rows as well.

 

[ollyhughes1982]

But why should F be Fountains Abbey parkrun, rather than Forest of Dean parkrun? That goes for the other rows as well.

I don’t know what code they have used to do that, but if possible I would want it so that of there are multiple ones that qualify for one, take the one done on the earliest date. Thanks

 

[Fluff]

I don’t know what code they have used to do that

Then how is a formula going to know?

I would want it so that of there are multiple ones that qualify for one, take the one done on the earliest date

Surely that's what your current formula does.