UNIQUE FILTER / IF amendment needed

O

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
677
Office Version
  1. 365
Platform
  1. MacOS
Hi,

Please see below link to a file I’m having an issue with. I already have a solution in my ‘All Completed Runs - Alphabet’ worksheet to find the first instance of each run that begins with the relevant letter. This works for 95% of cases, but it does not for all. For example, my list is showing ‘Mallards Pike parkrun’ in 2019 as my first ‘M’ run, but it should actually be ‘parkrun de Mandavit’ in 2016. it's because in French parkruns they have the parkrun bit before the main name of the run, so this messes up my filter, as it picks it up as a 'P', rather than an 'M'.

Is there a way I can get around this? I do have a helper column (AU) in my main ‘All Completed Runs’ worksheet, which I have highlighted in yellow, which gives the first letter followed by an instance number. I wonder if I could use this to filter somehow, by having some sort of if or unique filter that first looks at the letter in AU and then if it is the first instance (i.e. if it is followed by 1). This would then give me the correct result. I just don’t know how to do it.

Link: Unique Filter issue.xlsx

Thanks in advance!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
How about
=INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,'All Completed Runs'!$AS$3:$AS$2002=A3,"")),1)
 
Upvote 0
Hi. No that didn't work, sorry. It gives Aberbeeg. I have updated the file with that formula in, so you can see. I need cell B15 to find the first instance of an 'M' event. This should be parkrun de Mandavit on 04/06/2016, rather than Mallards Pike. =IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="M"),0),SEQUENCE(1)),"") was the formula previously. I think a 'LEFT' part might be missing?
Thanks again.
 
Upvote 0
Works for me
Unique Filter issue.xlsx
ABCD
2Alphabeteer LetterEvent (Venue)Date Completed
3AAberbeeg parkrun03/11/2018Aberbeeg parkrun
4BBryn Bach parkrun07/07/2018Bryn Bach parkrun
5CColby parkrun20/05/2017Colby parkrun
6DColby parkrun20/05/2017Dudley parkrun
7EEastville parkrun22/06/2019Eastville parkrun
8FForest of Dean parkrun07/09/2019Forest of Dean parkrun
9GGrangemoor parkrun02/02/2019Grangemoor parkrun
10HHereford parkrun06/07/2019Hereford parkrun
11I   
12J   
13K  parkrun Kraków
14LLydney parkrun20/07/2019Lydney parkrun
15MMallards Pike parkrun12/10/2019parkrun de Mandavit
16NNewport parkrun09/04/2011Newport parkrun
17O   
18Pparkrun de Mandavit04/06/2016Penallta parkrun
19Q   
20RRiverfront parkrun21/10/2017Riverfront parkrun
21SSevern Bridge parkrun11/08/2018Severn Bridge parkrun
22TTremorfa parkrun23/11/2019Tremorfa parkrun
23U   
24V   
25WWoolacombe Dunes parkrun17/08/2019Woolacombe Dunes parkrun
26X   
27Y   
28Z   
All Completed Runs - Alphabet
Cell Formulas
RangeFormula
B3B3=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="A"),0),SEQUENCE(1)),"")
C3:C28C3=IFERROR(VLOOKUP(B3#,'All Completed Runs'!$C$3:$E$2002,3,0),"")
D3:D28D3=INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,'All Completed Runs'!$AS$3:$AS$2002=A3,"")),1)
B4B4=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="B"),0),SEQUENCE(1)),"")
B5:B6B5=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="C"),0),SEQUENCE(1)),"")
B7B7=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="E"),0),SEQUENCE(1)),"")
B8B8=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="F"),0),SEQUENCE(1)),"")
B9B9=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="G"),0),SEQUENCE(1)),"")
B10B10=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="H"),0),SEQUENCE(1)),"")
B11B11=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="I"),0),SEQUENCE(1)),"")
B12B12=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="J"),0),SEQUENCE(1)),"")
B13B13=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="K"),0),SEQUENCE(1)),"")
B14B14=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="L"),0),SEQUENCE(1)),"")
B15B15=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="M"),0),SEQUENCE(1)),"")
B16B16=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="N"),0),SEQUENCE(1)),"")
B17B17=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="O"),0),SEQUENCE(1)),"")
B18B18=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="P"),0),SEQUENCE(1)),"")
B19B19=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="Q"),0),SEQUENCE(1)),"")
B20B20=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="R"),0),SEQUENCE(1)),"")
B21B21=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="S"),0),SEQUENCE(1)),"")
B22B22=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="T"),0),SEQUENCE(1)),"")
B23B23=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="U"),0),SEQUENCE(1)),"")
B24B24=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="V"),0),SEQUENCE(1)),"")
B25B25=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="W"),0),SEQUENCE(1)),"")
B26B26=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="X"),0),SEQUENCE(1)),"")
B27B27=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="Y"),0),SEQUENCE(1)),"")
B28B28=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,LEFT('All Completed Runs'!$C$3:$C$2002,1)="Z"),0),SEQUENCE(1)),"")
 
Upvote 0
Is there a way to get the date to work as well, please? The date should be 04/06/2016
 
Upvote 0
I'm not sure that UNIQUE() adds anything to that formula. Column E appears to do the same job.

ollyhughes1982Unique Filter issue.xlsx
ABDE
3AAberbeeg parkrunAberbeeg parkrunAberbeeg parkrun
4BBryn Bach parkrunBryn Bach parkrunBryn Bach parkrun
5CColby parkrunColby parkrunColby parkrun
6DColby parkrunDudley parkrunDudley parkrun
7EEastville parkrunEastville parkrunEastville parkrun
8FForest of Dean parkrunForest of Dean parkrunForest of Dean parkrun
9GGrangemoor parkrunGrangemoor parkrunGrangemoor parkrun
10HHereford parkrunHereford parkrunHereford parkrun
11I  
12J  
13Kparkrun Krakówparkrun Kraków
14LLydney parkrunLydney parkrunLydney parkrun
15MMallards Pike parkrunparkrun de Mandavitparkrun de Mandavit
16NNewport parkrunNewport parkrunNewport parkrun
17O  
18Pparkrun de MandavitPenallta parkrunPenallta parkrun
19Q  
20RRiverfront parkrunRiverfront parkrunRiverfront parkrun
21SSevern Bridge parkrunSevern Bridge parkrunSevern Bridge parkrun
22TTremorfa parkrunTremorfa parkrunTremorfa parkrun
23U  
24V  
25WWoolacombe Dunes parkrunWoolacombe Dunes parkrunWoolacombe Dunes parkrun
26X  
27Y  
28Z  
All Completed Runs - Alphabet
Cell Formulas
RangeFormula
D3:D28D3=INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,'All Completed Runs'!$AS$3:$AS$2002=A3,"")),1)
E3:E28E3=INDEX(FILTER('All Completed Runs'!$C$3:$C$2002,'All Completed Runs'!$AS$3:$AS$2002=A3,""),1)
 
Upvote 0
Thanks both. Is there a way to also get the date to match, as similar serach but, pull the relevant date, rather than the event name
 
Upvote 0
Try

=INDEX(FILTER('All Completed Runs'!C$3:C$2002,('All Completed Runs'!AS$3:AS$2002=A3)*('All Completed Runs'!E$3:E$2002=C3),""),1)
 
Upvote 0
Peter_SSs said:
I'm not sure that UNIQUE() adds anything to that formula. Column E appears to do the same job.
Click to expand...
Agreed. I pretty much just used the OP's formula.

@ollyhughes1982
Maybe
=INDEX(FILTER('All Completed Runs'!$E$3:$E$2002,'All Completed Runs'!$AS$3:$AS$2002=A3,""),1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Counting instances of unique entries, with conditions
Replies
1
Views
383
ollyhughes1982
O
O
  • Solved
XLOOKUP to find Nth occurrence
Replies
4
Views
1K
ollyhughes1982
O
O
  • Solved
COUNTIFS including
Replies
1
Views
119
ollyhughes1982
O
O
  • Solved
AGGREGATE function not working as expected
Replies
1
Views
270
ollyhughes1982
O
O
  • Solved
Find records containing some characters but not others
Replies
1
Views
425
ollyhughes1982
O

Forum statistics

Threads
1,214,522
Messages
6,120,020
Members
448,938
Latest member
Aaliya13

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top