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]

This has been up for a while now and haven't been able to find a solution. I will give up on this one. Thanks, everyone, for trying.

 

[RoryA]

Please don't mark a post as a solution if it doesn't contain an answer.

I've only just seen this question, but it seems to me that your issue with formulas is going to be that you need to consider all of the matches for a given letter with all of the matches for elements starting with that letter. To give a simple example, the elements for O are Oxygen, Osmium, and Oganesson. Your 'O' parkruns are Oxford, Old Deer, Örebro (I assume you include this) and Oaklands. Oaklands would match both Oxygen and Osmium but if you used it for Oxygen, you remove the only possible match for Osmium, so you are one down on possible matches you could have made. At the very least any formula approach is going to need to start by considering 2 letter matches first, and ideally at least prioritise elements that only have one match. Even that won't be fool-proof - I suspect you'd need a recursive lambda or code to be 100% sure - but I think it would probably be close.

 

[ollyhughes1982]

Please don't mark a post as a solution if it doesn't contain an answer.

I've only just seen this question, but it seems to me that your issue with formulas is going to be that you need to consider all of the matches for a given letter with all of the matches for elements starting with that letter. To give a simple example, the elements for O are Oxygen, Osmium, and Oganesson. Your 'O' parkruns are Oxford, Old Deer, Örebro (I assume you include this) and Oaklands. Oaklands would match both Oxygen and Osmium but if you used it for Oxygen, you remove the only possible match for Osmium, so you are one down on possible matches you could have made. At the very least any formula approach is going to need to start by considering 2 letter matches first, and ideally at least prioritise elements that only have one match. Even that won't be fool-proof - I suspect you'd need a recursive lambda or code to be 100% sure - but I think it would probably be close.

Hi. Yes, thank you, you are understanding what I am after. I have spoken to the developers who have done it in their code (which they won’t divulge to me - which is fair enough, as they’ve put the work in to solve the problem) and they believe it not possible to do in Excel. However, they may not be aware of these latest recursive Lamda functions. What you’re saying is dead on, it’s like it needs to loop back over and amend entries if that entry is required for another entry and this entry can be filled by another. It’s very difficult and beyond my skills. If you have any ideas on how to do it, that would be brilliant, or anyone who might. Thanks.

 

[Sanjeev1976]

Hello,

I have worked on the file and created a check sheet for cross checking the details and found that the manual inputs are not always correct. Can you look at the updated file.

Have uploaded the file on wetransfer. Periodic Table

 

[ollyhughes1982]

Hello,

I have worked on the file and created a check sheet for cross checking the details and found that the manual inputs are not always correct. Can you look at the updated file.

Have uploaded the file on wetransfer. Periodic Table

I don't have wetransfer, I'm afraid. The manual entries might differ, there ar often multiple that can satisfy one entry

 

[Fluff]

@Sanjeev1976 can you please post what you have done to the board, so that all can see. As per Rule#4

 

[Sanjeev1976]

Hi Fluff,

I have already uploaded the file on WeTransfer and shared the link in my previous post for anyone to download. I have created a check sheet in the file for ollyhughes1982 to verify the manual inputs which were there in his file.

Hope I have replied to your message. Please let me know, how else I can upload the file.

ollyhughes1982, you don't need to have a WeTransfer account. You can simply click the link and download the file.

 

[Fluff]

Please let me know, how else I can upload the file.

You can use the XL2BB add-in. Files hosted on share sites do not normally stay for long, so we need to see what you have done.

 

[Sanjeev1976]

I'm aware about XL2BB and have used it lot of times here, but there is so much data in the file that it will be difficult to upload using Xl2BB.

 

[Fluff]

All you need to do is post what you have done for the check sheet.