last in column for text

[TitansFan]

I'm wanting to use the =lastincolumn() funciton to return the last text entry in a column in a spreadsheet. How do I do this?

 

[MrKowz]

I've never heard of the lastincolumn() function, but you can do this with a lookup:

=LOOKUP(Rept("z",255),A:A)

 

[TitansFan]

Thank you, that works! I'm designing a spreadsheet for our company that will allow us to track our equipment that is used in the field. I needed a way to reference the last place the piece of equipment was used and have it show up on a summary page...in other words, the last entry in a column.

I'll probably have more questions as I continue my work on this. I'll keep in mind the "Search" option first to find the answer.

 

[TitansFan]

A couple of more questions...

How do I change the formula to display text OR a number? And how do I get it to display a date?

 

[MrKowz]

As far as getting it to display the last text OR number, we need to create a bit more robust of a formula:

=INDEX(A:A,MAX(MATCH(9.99E+307,A:A),MATCH(REPT("z",255),A:A)))

To get it to find a date... I don't believe that it will be possible to determine the difference between a date and a number, since a date is actually a serial number of the number of days since 1/0/1900. For example, 1/4/1900 is actually stored as "4" in a cell.

 

[Aladin Akyurek]

A couple of more questions...

How do I change the formula to display text OR a number? And how do I get it to display a date?

For the last numeric value...

=LOOKUP(9.99999999999999E+307,A:A)

Dates are numbers, so they will be picked up by the foregoing formula.

If the last value of interest can be either text or number...

B1:

=MATCH(REPT("z",255),A:A)

B2:

=MATCH(9.99999999999999E+307,A:A)

B3:

=INDEX(A:A,IF(COUNT(B1:B2)=2,MAX(B1:B2),SUMIF(B1:B2,">0")))

 

[Aladin Akyurek]

As far as getting it to display the last text OR number, we need to create a bit more robust of a formula:

=INDEX(A:A,MAX(MATCH(9.99E+307,A:A),MATCH(REPT("z",255),A:A)))

To get it to find a date... I don't believe that it will be possible to determine the difference between a date and a number, since a date is actually a serial number of the number of days since 1/0/1900. For example, 1/4/1900 is actually stored as "4" in a cell.

MAX'ing like this won't work when column A consist of either just numbers or just text...

 

[MrKowz]

MAX'ing like this won't work when column A consist of either just numbers or just text...

Thanks for pointing that out, Aladin, I did a brief testing and hadn't made that assumption.

Try:

=INDEX(A:A,MAX(IF(ISERROR(MATCH(9.99E+307,A:A)),1,MATCH(9.99E+307,A:A)),IF(ISERROR(MATCH(REPT("z",255),A:A)),1,MATCH(REPT("z",255),A:A))))

 

[Aladin Akyurek]

Thanks for pointing that out, Aladin, I did a brief testing and hadn't made that assumption.

Try:

=INDEX(A:A,MAX(IF(ISERROR(MATCH(9.99E+307,A:A)),1,MATCH(9.99E+307,A:A)),IF(ISERROR(MATCH(REPT("z",255),A:A)),1,MATCH(REPT("z",255),A:A))))

Try to avoid computing twice...

 

[TitansFan]

Thanks!