INDEX AGGREGATE issue

[ollyhughes1982]

Hi,

I’m having trouble trying to get corresponding cells to display in columns H & I in my ‘Completed Unique E(V)’ worksheet. It should copy over the relevant cells from the ‘All Completed Runs’ worksheet, as do the other columns (i.e. the first time each event is completed). Please can someone identify what I’m doing wrong?

Link to the file is below:

Link: parkrun - My parkrun Record - WORKING.xlsx

Thanks in advance!

 

[Toadstool]

 

[ollyhughes1982]

Apologies for that. Please see cut-down version link, below:

Link: parkrun - My parkrun Record - WORKING - CUT DOWN.xlsx

 

[ollyhughes1982]

Also need a formula column J 'Region', please.

 

[Toadstool]

Hi OllyHughes1982,

I see two challenges:
1. Your INDEX is 'All Completed Runs'!$C$3:$AJ$2002 but you're trying to retrieve from columns BO and BP so the range needs to go to at least BP.
2. Your hardcoded columns 64 and 65 are off by 1. I think they need to be 65 and 66.

Also I believe the curly brackets for the array entered formula are unnecessary.

 

[ollyhughes1982]

Hi OllyHughes1982,

I see two challenges:
1. Your INDEX is 'All Completed Runs'!$C$3:$AJ$2002 but you're trying to retrieve from columns BO and BP so the range needs to go to at least BP.
2. Your hardcoded columns 64 and 65 are off by 1. I think they need to be 65 and 66.

Also I believe the curly brackets for the array entered formula are unnecessary.

Thanks. Might you be able to suggest working, please? I can't seem to work it out.

Just to confirm: I need to display the relevant values from the ‘All Completed Runs’ worksheet (i.e. the first time each event is completed), in columns H, I, J & K.

Link: parkrun - My parkrun Record - WORKING - CUT DOWN 2.xlsx

Thanks.

 

[Peter_SSs]

@ollyhughes1982
You have Excel 365. Does your version of that have the FILTER() function?

 

[ollyhughes1982]

Also posted on Excel Forum: INDEX AGGREGATE issue

 

[ollyhughes1982]

@ollyhughes1982
You have Excel 365. Does your version of that have the FILTER() function?

Hi. Yes it does, but as I had done the rest of the page this way, for now, I was just trying to quickly use the same method for these new columns.

 

[Peter_SSs]

Well, I would suggest a change as you can then do it with one formula in one cell as far as I can see.

First though, you need to ensure that your headings in 'Completed Unique E(V)' exactly match those in 'All Completed Runs' Where I see a difference is in 'Year Completed' where one has a space between the two words and one has an Alt+Enter (CHAR(10)).

If the headings from 'Completed Unique .. ' are exactly the same as in 'All Completed ..' then try this in cell A3 only of 'Completed Unique ..'. The other results will automatically 'spill' tot the other columns/rows as required.
You will have to format the Date column as date

ollyhughes1982.xlsx
	A	B	C	D	E	F	G
1
2	Date Completed	Home Page Link	Course Page Link	Year Completed	Longitude (Decimal Degrees)	Latitude (Decimal Degrees)	Region
3	9/04/2011	https://www.parkrun.org.uk/newport	https://www.parkrun.org.uk/newport/course	2011	-3.030939	51.559479	Wales
4	4/06/2016	https://www.parkrun.fr/mandavit	https://www.parkrun.fr/mandavit/course	2016	-0.606219	44.766638	France
5	20/05/2017	https://www.parkrun.org.uk/colby	https://www.parkrun.org.uk/colby/course	2017	-4.669924	51.74064	Wales
6	21/10/2017	https://www.parkrun.org.uk/riverfront	https://www.parkrun.org.uk/riverfront/course	2017	-2.993317	51.588559	Wales
7	26/05/2018	https://www.parkrun.org.uk/penallta	https://www.parkrun.org.uk/penallta/course	2018	-3.245679	51.644889	Wales
8	7/07/2018	https://www.parkrun.org.uk/brynbach	https://www.parkrun.org.uk/brynbach/course	2018	-3.269548	51.781621	Wales
9	11/08/2018	https://www.parkrun.org.uk/severnbridge	https://www.parkrun.org.uk/severnbridge/course	2018	-2.664531	51.619014	Wales
10	18/08/2018	https://www.parkrun.org.uk/cwmbran	https://www.parkrun.org.uk/cwmbran/course	2018	-3.013498	51.656234	Wales
11	25/08/2018	https://www.parkrun.org.uk/pontypool	https://www.parkrun.org.uk/pontypool/course	2018	-3.028573	51.699213	Wales
12	29/09/2018	https://www.parkrun.org.uk/pontypridd	https://www.parkrun.org.uk/pontypridd/course	2018	-3.339028	51.602009	Wales
13	3/11/2018	https://www.parkrun.org.uk/aberbeeg	https://www.parkrun.org.uk/aberbeeg/course	2018	-3.144286	51.711165	Wales
14	8/12/2018	https://www.parkrun.pl/krakow	https://www.parkrun.pl/krakow/course	2018	19.922504	50.059123	Poland
15	2/02/2019	https://www.parkrun.org.uk/grangemoor	https://www.parkrun.org.uk/grangemoor/course	2019	-3.192412	51.454464	Wales
16	25/05/2019	https://www.parkrun.org.uk/ashton-court	https://www.parkrun.org.uk/ashton-court/course	2019	-2.639948	51.443592	South West England
17	1/06/2019	https://www.parkrun.org.uk/groe	https://www.parkrun.org.uk/groe/course	2019	-3.403113	52.150418	Wales
18	22/06/2019	https://www.parkrun.org.uk/eastville	https://www.parkrun.org.uk/eastville/course	2019	-2.554304	51.475453	South West England
Completed Unique E(V) (2)

Cell Formulas
Range	Formula
A3:G48	A3	=FILTER(FILTER('All Completed Runs'!A3:BQ2002,ISNUMBER(MATCH('All Completed Runs'!A2:BQ2,A2:J2,0))),'All Completed Runs'!AO3:AO2002=1)
Dynamic array formulas.