INDEX(FILTER function to create a list by number of characters

[ollyhughes1982]

Hi all,

I have a workbook, ‘What A Character parkrun Example’. In this workbook, I have my various parkruns that I’ve attended listed in the ‘All Completed Runs’ worksheet. I then have the ‘All Completed Runs - WAC’ worksheet, where I want to list the parkruns I have done, by the number of characters in their name. I want to start at 9 (the minimum possible amount of characters) and go up to 50 (currently the highest amount of characters in any parkrun name in the world).

I want to list the earliest dated event for that number of characters in each instance.

I know I’ll likely need some sort of index(filter function, but not quite sure how to do it. At present I have left in the function that I have used in another worksheet that looks for the first instance of a parkrun beginning with letters of the alphabet. I guess this can be amended to do what I am after.

Link to file here: What A Character parkrun Example.xlsx

Thanks in advance,

Olly.

 

[jasonb75]

Linked file is useless, anti-virus is freaking out and it's not working correctly in browser.

Gonna need a version that works to attempt a formula like that.

 

[ollyhughes1982]

Linked file is useless, anti-virus is freaking out and it's not working correctly in browser.

Gonna need a version that works to attempt a formula like that.

OK, never had an issue with OneDrive before. Try DropBox: What A Character parkrun Example.xlsx

Thanks.

 

[jasonb75]

I managed to get the first one open in the end, took a while though.

This formula does what you've asked for, enter it into B4 and fill down.

=IFERROR(LET(arr,SORT(UNIQUE(CHOOSE({1,2,3},'All Completed Runs'!$C$4:$C$2003,'All Completed Runs'!$E$4:$E$2003,LEN('All Completed Runs'!$C$4:$C$2003))),{3,2}),INDEX(arr,MATCH(A4,INDEX(arr,0,3),0),{1,2})),"")

edit:- same problem with dropbox, it's the file not the host site. Anti-virus is saying it's unsafe to open.

 

[ollyhughes1982]

I managed to get the first one open in the end, took a while though.

This formula does what you've asked for, enter it into B4 and fill down.

=IFERROR(LET(arr,SORT(UNIQUE(CHOOSE({1,2,3},'All Completed Runs'!$C$4:$C$2003,'All Completed Runs'!$E$4:$E$2003,LEN('All Completed Runs'!$C$4:$C$2003))),{3,2}),INDEX(arr,MATCH(A4,INDEX(arr,0,3),0),{1,2})),"")

edit:- same problem with dropbox, it's the file not the host site. Anti-virus is saying it's unsafe to open.

That worked perfectly. Thanks for your help.