UNIQUE FILTER / IF amendment needed

[ollyhughes1982]

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!

 

[ollyhughes1982]

Hi. I get a circular reference with that formula.

 

[ollyhughes1982]

Hi. Yes, thanks both. I have removed the unique for column B now, thanks. I just need to get the date field working, if possible, please. I got a circular reference with the suggested formula

 

[Peter_SSs]

Hi. I get a circular reference with that formula.

Yes, I misinterpreted the question I think. See Fluff's suggestion in post #10

 

[ollyhughes1982]

Brilliant. That's now working perfectly. Thanks, both

 

[ollyhughes1982]

Would it be ok to ask if you might be able to help with my other issue that I am having at the moment? Link to thread is below:

MINIFS with OFFSET

Hi all, I'm having trouble combining MINIFS with an OFFSET. What I want to do is find the minimum value (if it isn't 0 or a blank) in column 'I' ('Distance from Home parkrun (K)') of the 'Miscellaneous Data - AP' worksheet - I can do this fine with the following formula: =MINIFS('Miscellaneous...

www.mrexcel.com

Thanks.

 

[Fluff]

Glad we could help & thanks for the feedback