How to get last cell in a column in Array without VBA

[rex_411]

I have a column of data with varying length and I use the formula below to return a value. Is there a way to have excel determine the last cell in the column without using VBA? My assumption (I've tried a few times) is to use INDIRECT, but it keeps returning a numeric value (column is names).

Here is my current formula: {=AVERAGE(IF(('Paste Report HERE'!$A1:$A5000=E1),'Paste Report HERE'!$E1:$E5000))}

'Paste Report HERE' is the worksheet with a list of names in column A [Name 1, Name1, Name 2, Name 2]. Column E of that same sheet has a list of time [00:00:45, 00:00:36, 00:02:42, 00:00:55]

The value in cell E1 from tab where the formula resides is the name of the person whos times I'm trying to average [Name 1]

The unknown column length is the A and E column lengths. I set to 5000, but it could be anything. In VBA I could use LastRow or something to get the value but can I get it without VBA? I have several formulas in the spreadsheet which could use this same method.

Another example: (in this case 9000) to represent the last cell in the column. The formula below counts the unique instances of a value based on another value matching.

{=SUM(IF(FREQUENCY(IF($A$2:$A$9000=$L2,IF($J$2:$J$9000<>"",MATCH($J$2:$J$9000,$J$2:$J$9000,0))),ROW($J$2:$J$9000)-ROW($A$2)+1),1))}

Check Column A for value found in cell L2. Where ever there is a match look at column J and return the number of times a unique value is found. Column A = names & Column J is Dates. The Formula returns a number (like 6 if I run the report for a 6 day range and the person appears on all 6 days).

Thanks for the help!​

 

[Coding4Fun]

Not really sure how you will apply it to you current case but this will give you the last row in a column with data.

=ROW(INDEX(A:A, MAX(IFERROR(MATCH(1E+99, A:A), 1), IFERROR(MATCH("zzz", A:A), 1), 1)))

This will return 9 if the last row in Column A is in Cell A9

=ADDRESS(ROW(INDEX(A:A, MAX(IFERROR(MATCH(1E+99, A:A), 1), IFERROR(MATCH("zzz", A:A), 1), 1))),1)

This will return $A$9 if A9 was the last row with data, might help.

 

[Aladin Akyurek]

For this...

=AVERAGE(IF(('Paste Report HERE'!$A1:$A5000=E1),'Paste Report HERE'!$E1:$E5000))

in a convenient cell, maybe in F1, enter:

=MATCH(9.99999999999999E+307,'Paste Report HERE'!$E:$E)

Now the AVERAGE formula can be rewritten:

{=AVERAGE(IF(('Paste Report HERE'!$A1:INDEX('Paste Report HERE'!$A:$A,$F$1)=E1),'Paste Report HERE'!$E1:INDEX('Paste Report HERE'!$E:$E,$F$1)))}

For this

{=SUM(IF(FREQUENCY(IF($A$2:$A$9000=$L2,IF($J$2:$J$9000<>"",MATCH($J$2:$J$9000,$J$2:$J$9000,0))),ROW($J$2:$J$9000)-ROW($A$2)+1),1))}

in a convenient cell, maybe in L1, enter:

=MATCH(REPT("z",255),$J:$J)

Now you can rewrite the FREQUENCY formula as:

{=SUM(IF(FREQUENCY(IF($A$2:INDEX($A:$A,$L$1)=$L2,IF($J$2:INDEX($J:$J,$L$1)<>"",MATCH($J$2:INDEX($J:$J,$L$1),$J$2:INDEX($J:$J,$L$1),0))),ROW($J$2:INDEX($J:$J,$L$1))-ROW($J$2)+1),1))}


This set up creates dynamic ranges for the local use, that is, with a scope that applies to the formula sheet.


If we would want that the dynamic definitions should be accessible in the whole workbook, we need to involve the Name Manager. Is this what you would prefer?

 

[Aladin Akyurek]

@ Coding4Fun

You seem you have never heard of 9.99999999999999E+307 and REPT("z",255).

Even worse, you are suggesting terribly inefficient formulas with IFERROR and ADDRESS.

 

[rex_411]

Aladin,
Brilliant solution - I had some of the pieces but was unable to solve the puzzle. Worked like a charm! Your formula method will save me some CPU resources.

BTW, I was able to find another way to generate the last cell in a column (although I was unable to use it correctly): =LOOKUP(2,1/('Paste Report HERE'!A:A<>""),ROW('Paste Report HERE'!A:A))

 

[Aladin Akyurek]

Aladin,
Brilliant solution - I had some of the pieces but was unable to solve the puzzle. Worked like a charm! Your formula method will save me some CPU resources.

BTW, I was able to find another way to generate the last cell in a column (although I was unable to use it correctly): =LOOKUP(2,1/('Paste Report HERE'!A:A<>""),ROW('Paste Report HERE'!A:A))

That another way is also inefficient...

 

[Coding4Fun]

@ Coding4Fun

You seem you have never heard of 9.99999999999999E+307 and REPT("z",255).

Even worse, you are suggesting terribly inefficient formulas with IFERROR and ADDRESS.

@Aladin way to be a dik, noone knows everything that is why these forums exist was just trying to help. Wonder how you became an MVP with an attitude like that....Guess I will research 9.99999999999999E+307 and REPT("z",255) along with why IFERROR and ADDRESS is a problem since you didnt provide any info indicating what was wrong with my solution and how your comments apply, you are simply tossing out insults.

Real MVP go Aladin!

 

[Coding4Fun]

That another way is also inefficient...

Our awesome MVP at it again, tossing out insults with no explanation.....

Super MVP Aladin to the rescue!

 

[MARK858]

Guess I will research 9.99999999999999E+307 and REPT("z",255) along with why IFERROR and ADDRESS is a problem

Hi Coding4Fun, in case it helps your research in the link below Aladin explains BigNum (9.99999999999999E+307).
I'll leave it to him to elaborate on everything else.


https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a-post504775.html#post504775

 

[Coding4Fun]

That is how you provide positive constructive feedback. Thank you MARK858 appreciate it.