Long formula causing resource error and wanting to try a UDF but where to begin

[MikeyP14]

Hi All,

I have multiple formulas that are reading from one another and I currently have them setup to read all the data in a set column and I believe that is causing the sheet to run out of resources while computing. The max location and min location formulas are the ones I think a UDF could help. I was thinking if I could tell it what I want to evaluate and have it evaluate the data to the last row in the data tab that should save a significant amount of time computing and also should always change if the amount of data in the tab changes. I tried to figure out how to dynamically pick the last row in a dataset within excel formulas without going into VBA but I am thinking now that it might make sense to create a user defined formula but I have never messed around with one and it seems to be a bit more involved than my skillset allows for.

I have a summary sheet which is pulling information from a data tab depending on specific criteria

see table below that is in my summary sheet Assume "CN" is in cell A1 and "min location" is in cell F1

CN	sub category	max	min	max location	min location
CN 101	p	7​	0.4​	CN 101_3	CN 101_2
CN 101_1_T	p	4​	1​	CN 101_1	CN 101_1
CN 101_2_T	p	0.6​	0.4​	CN 101_2	CN 101_2
CN 101_3_T	p	7​	3​	CN 101_3	CN 101_3
CN 100		50​	50​	CN 100	CN 100
CN 55		90​	9​	CN 55_2	CN 55
CN 55_1_R		35​	35​	CN 55_1	CN 55_1
CN 55_2_R		90​	90​	CN 55_2	CN 55_2
CN 55_3_R		58​	58​	CN 55_3	CN 55_3
CN 70		92​	21​	CN 70_3	CN 70
CN 70_1_S		50​	50​	CN 70_1	CN 70_1
CN 70_2_S		65​	65​	CN 70_2	CN 70_2
CN 70_3_S		92​	92​	CN 70_3	CN 70_3

Calculations for row with CN 101
max =IFERROR(IF(COUNTIF(B2,"*p*"),(AGGREGATE(14,6,Data!$C:$D/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)),(AGGREGATE(14,6,Data!$B:$B/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1))),"no matches")

min =IF(COUNTIF(B2,"*p*"),(AGGREGATE(15,6,Data!$C:$D/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)),(AGGREGATE(15,6,Data!$B:$B/(LEFT(Data!$A:$A,LEN(A2))=A2&""),1)))

max location = (IF(COUNTIF(B2,"*p*"),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((C2=Data!$C:$C),(C2=Data!$C:$C),(C2=Data!$D:$D)),0))))))))),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(C2=Data!$B:$B),0)))))))))))

min location =(IF(COUNTIF(B2,"*p*"),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*IF((D2=Data!$C:$C),(D2=Data!$C:$C),(D2=Data!$D:$D)),0))))))))),(LEFT(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)),IFERROR(FIND("_S",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,IFERROR(FIND("_R",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,IFERROR(FIND("_T",INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))-1,(LEN(INDEX(Data!A:A,MATCH(1,(A2&""=LEFT(Data!$A:$A,LEN(A2)))*(D2=Data!$B:$B),0)))))))))))

Data tab

CN	Value 1	Value 2	value 3
CN 101	3​	2​	1​
CN 101_1_T	5​	1​	4​
CN 101_2_T	1​	0.6​	0.4​
CN 101_3_T	10​	7​	3​
CN 100	50​	30​	90​
CN 55	9​	75​	6​
CN 55_1_R	35​	50​	88​
CN 55_2_R	90​	1000​	2​
CN 55_3_R	58​	63​	678​
CN 70	21​	31​	9​
CN 70_1_S	50​	99​	45​
CN 70_2_S	65​	44​	78​
CN 70_3_S	92​	95​	235​

I would appreciate any guidance here. If this is not clear and you need additional information I will do my best to provide it.

Thanks in advance!

 

[offthelip]

All of your formula are using entire columns because you have specified them as $A:$A, etc. you might find that it uses a lot less resource if you specify where the bottom of the column is , even if it is in the thousands it is going to be less than million rows that excel can cope with

 

[MikeyP14]

Anyone have any suggestions on a UDF for this? I know the formulas are causing excel to run out of computing power because I have them reading A:A, B:B, etc... but that is why I was hoping someone might have a clever UDF for this since you make the UDF in VBA I was hoping you could tell it to compute to the last row of data in a sheet and then you would just need to make the input which column to look at within the sheet and it would automatically calculate to the last row.

Any advice is appreciated.

 

[RoryA]

Have you tried using Tables?

 

[offthelip]

Trying to work out what the udf needs to do just from your huge equations and no other information at all, is not a trivial task. Certainly determining the last row with any data in it is easily done in VBA. Did you try my suggestion?

 

[MikeyP14]

I have shortened my dataset to be less lines, which has worked, but since the formula is so long I was hoping I wouldn't have to go in and edit it every time I wanted to use it.