Issue with coordinates and ROUND(AGGREGATE

[ollyhughes1982]

Hi all,

In cell B4, I have a formula that calculates the average coordinates for the volunteering events at which I have volunteered so far.

I then have the below formulas in B5, B6 and B7, which determine the parkrun with the closest coordinates to the average calculated in B4:

B5:
=IFERROR(IF('All Completed VC'!D4="","",TEXTJOIN(", ",,FILTER('All Completed VC'!C4:D2003,AGGREGATE(15,6,'All Completed VC'!AQ4:AQ2003,1)='All Completed VC'!AQ4:AQ2003))),"")

B6:
=IFERROR(IF('All Completed VC'!D4="","",TEXTJOIN(", ",,FILTER('All Completed VC'!C4:D2003,AGGREGATE(15,6,'All Completed VC'!AQ4:AQ2003,1)='All Completed VC'!AQ4:AQ2003))&" - "&TEXT(ROUND(AGGREGATE(15,6,'All Completed VC'!AQ4:AQ2003,1),0),"#,##0")&"K Away"),"")

B7:
=IFERROR(IF('All Completed VC'!D4="","",TEXTJOIN(", ",,FILTER('All Completed VC'!C4:D2003,AGGREGATE(15,6,'All Completed VC'!AQ4:AQ2003,1)='All Completed VC'!AQ4:AQ2003))&" - "&TEXT(ROUND(AGGREGATE(15,6,'All Completed VC'!AR4:AR2003,1),0),"#,##0")&" Miles Away"),"")

My problem is that B5, B6 and B7 are showing a string of the same parkrun, but it should just be showing one. These same formulas work if I change the list of parkruns to a fictional list of other ones, but whenever I use my actual data I get this list with multiples of the same parkrun showing in a string. Just wondered if there was a way to resolve this, and to find out why this is happening- as it works if I populate the list with different events.

Could it be something to do with Newport parkrun and Newport junior parkrun being at the same location, and I have volunteered at both?

My file can be found in the links below, it would be too complicated to explain without having a link to the file. It’s only a small file and should open straight up.

One Drive: Volunteering Average example.xlsx.xlsx
Dropbox: Volunteering Average example.xlsx.xlsx

Thanks in advance,

Olly.

 

[Fluff]

Just wrap the filter function in Unique.

 

[ollyhughes1982]

Just wrap the filter function in Unique.

Thanks, that worked a treat. Brilliant, as ever.

 

[Fluff]

Glad to help & thanks for the feedback.