UNIQUE FILTER / IF amendment needed

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
269
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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
How about
=INDEX(UNIQUE(FILTER('All Completed Runs'!$C$3:$C$2002,'All Completed Runs'!$AS$3:$AS$2002=A3,"")),1)
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Works for me
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)),"")
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Apologies, forgot to change the cell reference. Thanks!
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS
Is there a way to get the date to work as well, please? The date should be 04/06/2016
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,913
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,913
Office Version
  1. 365
Platform
  1. Windows
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)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
I'm not sure that UNIQUE() adds anything to that formula. Column E appears to do the same job.
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,530
Messages
5,548,583
Members
410,854
Latest member
ajbaluyut
Top