replace array formula

[andycreber]

hi

I want to place this array formula as it is too slow to process.

please could you help me find an alternative solution formula



{=MIN(IF($P$2:$P$50000=P3,IF($U$2:$U$50000<>"Fuel Card",$AM$2:$AM$50000)))}

Many thanks

 

[FDibbins]

@ thisoldman, in this situation, I believe that AGGREGATE is still acting as an array formula, even though it is regularly entered

 

[andycreber]

sorry everyone that I am not explaining this very well andI have limited excel formula skills. I hope the below help explain the situation


[TABLE="width: 1459"]
<tbody>[TR]
[TD] a Number[/TD]
[TD]wagon[/TD]
[TD]Expected End Date[/TD]
[TD]min End Date?[/TD]
[TD]max End Date?[/TD]
[TD]Require Extension?[/TD]
[/TR]
[TR]
[TD="align: right"]214779[/TD]
[TD]MY11 (1/11) 1.6 TDI250 DSG Van[/TD]
[TD="align: right"]18/04/2020[/TD]
[TD]What is the min end date for cell a2 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a2 where coulmn B <> Fuel Card[/TD]
[TD]=IF(a2=a2,"No","Yes")'[/TD]
[/TR]
[TR]
[TD="align: right"]214295[/TD]
[TD]MY15 (8/14) 1.6 TDI250 DSG Van[/TD]
[TD="align: right"]14/04/2020[/TD]
[TD]What is the min end date for cell a3 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a3 where coulmn B <> Fuel Card[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]215520[/TD]
[TD]MY14 (1/14) 1.6 TDI250 FWD DSG Van[/TD]
[TD="align: right"]20/04/2020[/TD]
[TD]What is the min end date for cell a4 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a4 where coulmn B <> Fuel Card[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]216663[/TD]
[TD]Fuel Card[/TD]
[TD="align: right"]29/04/2020[/TD]
[TD]What is the min end date for cell a5 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a5 where coulmn B <> Fuel Card[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]214779[/TD]
[TD]Fuel Card[/TD]
[TD="align: right"]14/05/2021[/TD]
[TD]What is the min end date for cell a6 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a6 where coulmn B <> Fuel Card[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]215888[/TD]
[TD]T5 MY13 (1/13) 2.0 TDI340 LWB DSG Van[/TD]
[TD="align: right"]11/04/2020[/TD]
[TD]What is the min end date for cell a7 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a7 where coulmn B <> Fuel Card[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]215963[/TD]
[TD]T5 MY13 (1/13) 2.0 TDI340 LWB DSG Van[/TD]
[TD="align: right"]29/04/2020[/TD]
[TD]What is the min end date for cell a8 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a8 where coulmn B <> Fuel Card[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]216663[/TD]
[TD]Fuel Card[/TD]
[TD="align: right"]13/05/2022[/TD]
[TD]What is the min end date for cell a9 where coulmn B <> Fuel Card[/TD]
[TD]What is the max end date for cell a9 where coulmn B <> Fuel Card[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

 

[andycreber]

I don't want to use an array

 

[FDibbins]

Did you try adapting my suggestion (using a helper column) to your data?

 

[andycreber]

sorry, I didn't understand your last post, so I posted a new detailed update

 

[Aladin Akyurek]

Since you are copying the array formulas down to 50,000 rows twice, you get a huge performance problem.

Since you don't have MINIFS/MAXIFS, the following option might also be worth trying...

[TABLE="class: grid, width: 825"]
<tbody>[TR]
[TD]Number[/TD]
[TD]wagon[/TD]
[TD]Expected End Date[/TD]
[TD]min End Date?[/TD]
[TD]max End Date?[/TD]
[/TR]
[TR]
[TD]214779[/TD]
[TD]MY11 (1/11) 1.6 TDI250 DSG Van[/TD]
[TD]4/18/2020[/TD]
[TD]4/18/2020[/TD]
[TD]4/18/2020[/TD]
[/TR]
[TR]
[TD]214295[/TD]
[TD]MY15 (8/14) 1.6 TDI250 DSG Van[/TD]
[TD]4/14/2020[/TD]
[TD]4/14/2020[/TD]
[TD]4/14/2020[/TD]
[/TR]
[TR]
[TD]215520[/TD]
[TD]MY14 (1/14) 1.6 TDI250 FWD DSG Van[/TD]
[TD]4/20/2020[/TD]
[TD]4/20/2020[/TD]
[TD]4/20/2020[/TD]
[/TR]
[TR]
[TD]216663[/TD]
[TD]Fuel Card[/TD]
[TD]4/29/2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]214779[/TD]
[TD]Fuel Card[/TD]
[TD]5/14/2021[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]215888[/TD]
[TD]T5 MY13 (1/13) 2.0 TDI340 LWB DSG Van[/TD]
[TD]4/11/2020[/TD]
[TD]4/11/2020[/TD]
[TD]4/11/2020[/TD]
[/TR]
[TR]
[TD]215963[/TD]
[TD]T5 MY13 (1/13) 2.0 TDI340 LWB DSG Van[/TD]
[TD]4/29/2020[/TD]
[TD]4/29/2020[/TD]
[TD]4/29/2020[/TD]
[/TR]
[TR]
[TD]216663[/TD]
[TD]Fuel Card[/TD]
[TD]5/13/2022[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In D2 control+shift+enter, not just enter, and copy down:

=IF($B2="Fuel Card","",IF(ISNUMBER(MATCH($A2,$A$1:$A1,0)),INDEX($D$1:$D1,MATCH(A2,$A$1:A1,0)),MIN(IF($A$2:$A$9=$A2,IF(1-($B$2:$B$9="Fuel card"),$C$2:$C$9)))))

In E2 control+shift+enter, not just enter, and copy down:

=IF($B2="Fuel Card","",IF(ISNUMBER(MATCH($A2,$A$1:$A1,0)),INDEX($D$1:$D1,MATCH(A2,$A$1:A1,0)),MAX(IF($A$2:$A$9=$A2,IF(1-($B$2:$B$9="Fuel card"),$C$2:$C$9)))))

Note that these formulas avoid array re-computing the min and max values; they rather look up the value already array-computed instead of recomputing it.

 

[Marcelo Branco]

Another possible solution: DMIN and DMAX functions combined with What-If Analysis > Data Table

Something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Number​

[/TD]
[TD]

wagon​

[/TD]
[TD][/TD]
[TD]

Number​

[/TD]
[TD]

wagon​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

214779​

[/TD]
[TD]

<>Fuel Card​

[/TD]
[TD][/TD]
[TD]

214779​

[/TD]
[TD]

<>Fuel Card​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Min​

[/TD]
[TD]

18/04/2020​

[/TD]
[TD][/TD]
[TD]

Max​

[/TD]
[TD]

20/04/2020​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Number​

[/TD]
[TD]

wagon​

[/TD]
[TD]

Expected End Date​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

18/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"]

20/04/2020​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

214779​

[/TD]
[TD]

MY11 (1/11) 1.6 TDI250 DSG Van​

[/TD]
[TD]

18/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

214779​

[/TD]
[TD="bgcolor: #D9D9D9"]

18/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

214779​

[/TD]
[TD="bgcolor: #B8CCE4"]

20/04/2020​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

214295​

[/TD]
[TD]

MY15 (8/14) 1.6 TDI250 DSG Van​

[/TD]
[TD]

14/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

214295​

[/TD]
[TD="bgcolor: #D9D9D9"]

14/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

214295​

[/TD]
[TD="bgcolor: #B8CCE4"]

19/04/2020​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

215520​

[/TD]
[TD]

MY14 (1/14) 1.6 TDI250 FWD DSG Van​

[/TD]
[TD]

20/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

215520​

[/TD]
[TD="bgcolor: #D9D9D9"]

20/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

215520​

[/TD]
[TD="bgcolor: #B8CCE4"]

20/04/2020​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

216663​

[/TD]
[TD]

Fuel Card​

[/TD]
[TD]

29/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

216663​

[/TD]
[TD="bgcolor: #D9D9D9"]

Not Found​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

216663​

[/TD]
[TD="bgcolor: #B8CCE4"]

Not Found​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

214779​

[/TD]
[TD]

Fuel Card​

[/TD]
[TD]

14/05/2021​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

215888​

[/TD]
[TD="bgcolor: #D9D9D9"]

11/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

215888​

[/TD]
[TD="bgcolor: #B8CCE4"]

11/04/2020​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

215888​

[/TD]
[TD]

T5 MY13 (1/13) 2.0 TDI340 LWB DSG Van​

[/TD]
[TD]

11/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

215963​

[/TD]
[TD="bgcolor: #D9D9D9"]

22/04/2020​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

215963​

[/TD]
[TD="bgcolor: #B8CCE4"]

29/04/2020​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

215963​

[/TD]
[TD]

T5 MY13 (1/13) 2.0 TDI340 LWB DSG Van​

[/TD]
[TD]

29/04/2020​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

216663​

[/TD]
[TD]

Fuel Card​

[/TD]
[TD]

13/05/2022​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

214779​

[/TD]
[TD]

MY11 (1/11) 1.6 TDI250 DSG Van​

[/TD]
[TD]

20/04/2020​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

214295​

[/TD]
[TD]

MY15 (8/14) 1.6 TDI250 DSG Van​

[/TD]
[TD]

19/04/2020​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

215963​

[/TD]
[TD]

T5 MY13 (1/13) 2.0 TDI340 LWB DSG Van​

[/TD]
[TD]

22/04/2020​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Put the criteria in E1:F2 and in H1:I2

Formula in F3
=DMIN(A5:C50000,C5,$E$1:$F$2)

in F5
=F3

Create a list of unique values of column A (Number) - you can easily achieve this copying all the values in column A (not the header) to E6 and using Data > Remove duplicates

Select the cells in columns E:F that contain the formula in F6 and all the values in column E (gray area in the example)
go to Data > What-if Analysis> Data Table
leave blank the field Row input cell
put $E$2 in the field Column input cell

Select the results, cells F6:F11 in the example, and format
Custom
dd/mm/yyyy;General;"Not Found"

Do the same to get the Max (I6:I11 in the example)
Formula in I3
=DMAX(A5:C50000,C5,$H$1:$I$2)

in I5
=I3

etc, etc

M.