INDEX AGGREGATE issue

[ollyhughes1982]

Hi all,

I have an issue with my Excel file formula. it was previously working, but seems to have now gone wrong since making some changes to my workbook. It's cell B48 in the 'All Completed Runs - Summary' worksheet. The cell should display 'Trelai Park parkrun' then ',' then 'United Kingdom - Wales', [From column D, in the 'All Completed Runs' worksheet], then the value from cell A47, then 'Away'. i.e. Concatenate the three fields, with a comma and a space, after the first and 'Away' at the end.

What the cell should do, is display the above, but only if these two conditions are met:
- It is not a junior event (i.e. blank in column V of the 'Miscellaneous Data - AP' worksheet)
- Has not yet been completed (i.e. blank in column K of the 'Miscellaneous Data - AP' worksheet)

The result should be Trelai Park, which is 14k away from my home event of Coed Cefn-pwll-du. At the moment, this formula (when it worked) was giving me Newport junior parkrun, which it shouldn't do, as that is a junior event.

Link: parkrun - My parkrun Record - WORKING.xlsx

Thanks in advance!

 

[KRice]

In the file that you posted, the formula in 'All Completed Runs - Summary'!B48 is
=INDEX('Miscellaneous Data - AP'!B3:B2002,LARGE(--IF('Miscellaneous Data - AP'!I3:I2002=AGGREGATE(15,6,IF('Miscellaneous Data - AP'!I3:I2002>0,'Miscellaneous Data - AP'!I3:I2002),1),ROW(1:1998)),1))

That formula does not point to any data on the sheet that you described in your post #1 ('All Completed Runs'). Similar data are found on the 'Miscellaneous Data - AP' sheet, but it sounds as if you want the data to be taken from the 'All Completed Runs' worksheet? Would you clarify this, please? The formula above finds the smallest distance from home (column I), but it does not check column V (junior event) or column K (completion). I wanted to call your attention to something: distance from home information can be found on the 'Miscellaneous Data - AP' worksheet, and that is necessary to extract the information you want, but the same distance information is not found on the 'All Completed Runs" worksheet. Did you initially pull data from 'Miscellaneous Data - AP', and now wish to pull the information from 'All Completed Runs' instead? If so, then it would make sense to move the distance calculations---based on the Spherical Law of Cosines calculations in the 'Miscellaneous Data - AP' worksheet---to the 'All Completed Runs' worksheet. Let me know if this is what you had in mind...or did you want to use a much more complicated approach where the 'All Completed Runs - Summary' formula finds a venue based on distance in the 'Miscellaneous Data - AP' worksheet, and then that venue needs to be found in the 'All Completed Runs' worksheet in order to return information from the 'All Completed Runs' worksheet.

The following revision of the formula in 'All Completed Runs - Summary'!B48 checks for the two conditions you described and then finds the minimum distance from home (column I)...and then uses that distance in the MATCH function to find the array index number so that the corresponding event venue (column B) can be returned:

=INDEX('Miscellaneous Data - AP'!$B$3:$B$2002,MATCH(AGGREGATE(15,6,('Miscellaneous Data - AP'!$I$3:$I$2002)/(('Miscellaneous Data - AP'!$V$3:$V$2002="")*('Miscellaneous Data - AP'!$K$3:$K$2002="")*('Miscellaneous Data - AP'!$I$3:$I$2002>0)),1),'Miscellaneous Data - AP'!$I$3:$I$2002,0))

...but note that it is using information found in the 'Miscellaneous Data - AP' worksheet, not the 'All Completed Runs' worksheet. The result from the formula is Telai Park parkrun, which has the smallest distance from home value while satisfying the other conditions specified.

Let me know about the discrepancy above and whether you need assistance with extracting information for other cells in the worksheet...but it would be good to have a consistent source to pull data from.

 

[Fluff]

Another option, with dynamic arrays
=INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!U3:U2002=1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3>0)),8,1),1,1)
and
=TEXTJOIN(", ",,INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!U3:U2002=1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3>0)),8,1),1,{1,3,8})) & "K Away"

 

[Fluff]

If you want to keep the distance to whole numbers, use
=TEXTJOIN(", ",,INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!U3:U2002=1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3>0)),8,1),1,{1,3}))& " - "&TEXT(INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!U3:U2002=1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3>0)),8,1),1,8),"0")&"K Away"

 

[KRice]

Fluff's suggestions should be even better, and I believe your version of Excel will support the enhanced function set (SORT and FILTER). One point...I think a couple of adjustments may be needed to Fluff's formula: Column V indicates whether an event is a "junior event" and if so, a 1 is used, so I think you want cases where V<>1, like this:

=INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!V3:V2002<>1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3>0)),8,1),1,1)

 

[ollyhughes1982]

Thank you both, this is brilliant. Fluff: As per KRice's comment above, does your formula account for events not being junior ones?

 

[ollyhughes1982]

Actually, I think Fluff's does achieve the same thing, as having a one in column U is essentially the same as having <>1 in column V - as they are mutually exclusive: If it isn't 5K then it is definitely a junior event. Thanks both. That worked perfectly.

 

[Fluff]

Just realised that not only did I look in the wrong column, as pointed out by Kirk, but the formula is wrong anyway. as it's only looking at I3, not the entire column. It should be
=INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!U3:U2002=1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3:I2002>0)),8,1),1,1)

 

[ollyhughes1982]

Great team work, thanks. So what would the final complete formula be? I currently have =TEXTJOIN(", ",,INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!U3:U2002=1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3>0)),8,1),1,{1,3}))& " - "&TEXT(INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!U3:U2002=1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3>0)),8,1),1,8),"0")&"K Away"

 

[Fluff]

You need to extend the range as I showed in red, in post#8