Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find Last Row in Column
Hi There,
Is it possible with a formula to determine what the last row is in a certain column which is "Not Empty"? Brgds Sige |
#2
|
|||
|
|||
Find Last Row in Column
Hello Sige,
The formula =COUNT(A:A)+1 counts the number off filled cells in the column. This matches the first row within a the column A that is not empty. Hope you find this information useful gr. TemplateBuilder |
#3
|
|||
|
|||
Find Last Row in Column
Hello Sige,
The formula =COUNT(A:A)+1 counts the number off filled cells in the column. This matches the first row within a the column A that is not empty. Hope you find this information useful gr. TemplateBuilder |
#4
|
|||
|
|||
Find Last Row in Column
Hi TemplateBuilder,
Thank you for your solution but that is not exactly what I am after ... Imagine you have only 5 values filled in Column A ...bu the 5th is at row 125 then I would like it to return 125 and not 5 (or 6). Brgds Sige |
#5
|
|||
|
|||
Find Last Row in Column
Is it a numeric column with formulas or without?
wrote: Hi There, Is it possible with a formula to determine what the last row is in a certain column which is "Not Empty"? Brgds Sige |
#6
|
|||
|
|||
Find Last Row in Column
Aladin,
A column with text- and numerci values. No formulas though. Brgds Sige |
#7
|
|||
|
|||
Find Last Row in Column
One way* ..
Put in say, B1, array-enter (press CTRL+SHIFT+ENTER): =MAX(IF(ISNUMBER(A1:A65535),ROW(A1:A65535))) *inspired by the excellent white paper by Frank Kabel & Bob Phillips at: http://www.xldynamic.com/source/xld.....html#last_any -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- wrote in message oups.com... Hi TemplateBuilder, Thank you for your solution but that is not exactly what I am after ... Imagine you have only 5 values filled in Column A ...bu the 5th is at row 125 then I would like it to return 125 and not 5 (or 6). Brgds Sige |
#8
|
|||
|
|||
Find Last Row in Column
Hi
One way =CELL("Address",INDEX(A:A,MAX(MATCH(99999999,A:A), MATCH("ZZZZ",A:A)))) This will look for the last numeric or last text value in column A and return the cell reference where found. Some people use the largest number Excel can handle (9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for text, but I have usually found the above to meet my needs. Regards Roger Govier wrote: Hi TemplateBuilder, Thank you for your solution but that is not exactly what I am after ... Imagine you have only 5 values filled in Column A ...bu the 5th is at row 125 then I would like it to return 125 and not 5 (or 6). Brgds Sige |
#9
|
|||
|
|||
Find Last Row in Column
Hi Max,
Thanks for your solution & the link .... *inspired by the excellent white paper by Frank Kabel & Bob Phillips at: http://www.xldynamic.com/source/xld.....html#last_any A GREAT PAPER INDEED! Brgds Sige |
#11
|
|||
|
|||
Find Last Row in Column
You're welcome !
A GREAT PAPER INDEED! Sentiments shared, though not w/o a tinge of sadness* *in memory of Frank Kabel -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- wrote in message oups.com... Hi Max, Thanks for your solution & the link .... *inspired by the excellent white paper by Frank Kabel & Bob Phillips at: http://www.xldynamic.com/source/xld.....html#last_any A GREAT PAPER INDEED! Brgds Sige |
#12
|
|||
|
|||
Find Last Row in Column
Taking column A as target...
1. Focusing only on data that is either numeric or text... B1: =MATCH(REPT("z",255),A:A) B2: =MAX(CHOOSE({1,2},IF(ISNUMBER(B1),B1,0),MATCH(9.99 999999999999E+307,A:A))) 2. Focus is on any value... =IF(ISBLANK(A65536),MATCH(2,1/(1-ISBLANK(A1:A65535))),65536) which must be confirmed with control+shift+enter. This yields the native row number of even an error value if that is the last value. Taking in consideration your data types, he first is more efficient. wrote: Aladin, A column with text- and numerci values. No formulas though. Brgds Sige |
#13
|
|||
|
|||
Find Last Row in Column
Two notes...
This will fail when there is either no text or no numeric data. BTW, the OP is asking for the row number, not the address of the last numeric/text value. You state: "Some people use the largest number Excel can handle (9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for text, but I have usually found the above to meet my needs." Maybe so. However, did you consider endless variations the people may come up with, causing a jungle of constants, hard to explain to new comers? Roger Govier wrote: Hi One way =CELL("Address",INDEX(A:A,MAX(MATCH(99999999,A:A), MATCH("ZZZZ",A:A)))) This will look for the last numeric or last text value in column A and return the cell reference where found. Some people use the largest number Excel can handle (9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for text, but I have usually found the above to meet my needs. Regards Roger Govier wrote: Hi TemplateBuilder, Thank you for your solution but that is not exactly what I am after ... Imagine you have only 5 values filled in Column A ...bu the 5th is at row 125 then I would like it to return 125 and not 5 (or 6). Brgds Sige -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#14
|
|||
|
|||
Find Last Row in Column
Hi Aladin
Thank you for the notes. This will fail when there is either no text or no numeric data. I had rather assumed, that if the OP had a totally empty column, then he wouldn't be looking for the last used row, hence the #N/A return from a totally blank column didn't really seem to be material. BTW, the OP is asking for the row number, not the address Yes, on re-reading the original post, the OP does ask for row and not cell address. The formula should be amended to =CELL("Row",INDEX(A:A,MAX(MATCH(99999999,A:A),MATC H("ZZZZ",A:A)))) However, did you consider endless variations the people may come up with, causing a jungle of constants, hard to explain to new comers? No, I didn't. But I did give the formula for a large numeric value or large text value if the user chose to use those instead. Neither did I go on to explain that in reality, I use defined names for large numbers and large text values, which I use in my formulae rather than having to count how many 9's I am entering every time I use this type of Lookup, and enabling the call of the REPT() function to be made once, rather than throughout every formula. InsertNameDefine Name BigN Refers to =9.99999999999999E+307 Name BigT Refers to =REPT("Z",255) (these were tips I picked up from Bob Phillips) =CELL("Row",INDEX(A:A,MAX(MATCH(BigN,A:A),MATCH(Bi gT,A:A)))) I think it is sometimes of more value to the OP to get a rapid answer that answers their immediate need, rather than wait longer for a comprehensive answer that suits everyone. But then again, perhaps I am wrong.<g Regards Roger Govier Aladin Akyurek wrote: Two notes... This will fail when there is either no text or no numeric data. BTW, the OP is asking for the row number, not the address of the last numeric/text value. You state: "Some people use the largest number Excel can handle (9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for text, but I have usually found the above to meet my needs." Maybe so. However, did you consider endless variations the people may come up with, causing a jungle of constants, hard to explain to new comers? Roger Govier wrote: Hi One way =CELL("Address",INDEX(A:A,MAX(MATCH(99999999,A:A), MATCH("ZZZZ",A:A)))) This will look for the last numeric or last text value in column A and return the cell reference where found. Some people use the largest number Excel can handle (9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for text, but I have usually found the above to meet my needs. Regards Roger Govier wrote: Hi TemplateBuilder, Thank you for your solution but that is not exactly what I am after ... Imagine you have only 5 values filled in Column A ...bu the 5th is at row 125 then I would like it to return 125 and not 5 (or 6). Brgds Sige |
#15
|
|||
|
|||
Find Last Row in Column
Roger Govier wrote:
Hi Aladin Thank you for the notes. This will fail when there is either no text or no numeric data. I had rather assumed, that if the OP had a totally empty column, then he wouldn't be looking for the last used row, hence the #N/A return from a totally blank column didn't really seem to be material. That is not what I mean: When it's the case that A is all numeric or when when A is all text, the formula will fail. BTW, the OP is asking for the row number, not the address Yes, on re-reading the original post, the OP does ask for row and not cell address. The formula should be amended to =CELL("Row",INDEX(A:A,MAX(MATCH(99999999,A:A),MATC H("ZZZZ",A:A)))) Too wordy... <g and still subject to the criticism I forwarded. However, did you consider endless variations the people may come up with, causing a jungle of constants, hard to explain to new comers? No, I didn't. But I did give the formula for a large numeric value or large text value if the user chose to use those instead. Neither did I go on to explain that in reality, I use defined names for large numbers and large text values, which I use in my formulae rather than having to count how many 9's I am entering every time I use this type of Lookup, and enabling the call of the REPT() function to be made once, rather than throughout every formula. InsertNameDefine Name BigN Refers to =9.99999999999999E+307 Name BigT Refers to =REPT("Z",255) (these were tips I picked up from Bob Phillips) I for one often called them: BigNum and BigStr. =CELL("Row",INDEX(A:A,MAX(MATCH(BigN,A:A),MATCH(Bi gT,A:A)))) I think it is sometimes of more value to the OP to get a rapid answer that answers their immediate need, rather than wait longer for a comprehensive answer that suits everyone. But then again, perhaps I am wrong.<g You are... <vbg. Seriously, I admit I tend to be kosher about things like that. Regards Roger Govier Aladin Akyurek wrote: Two notes... This will fail when there is either no text or no numeric data. BTW, the OP is asking for the row number, not the address of the last numeric/text value. You state: "Some people use the largest number Excel can handle (9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for text, but I have usually found the above to meet my needs." Maybe so. However, did you consider endless variations the people may come up with, causing a jungle of constants, hard to explain to new comers? Roger Govier wrote: Hi One way =CELL("Address",INDEX(A:A,MAX(MATCH(99999999,A:A), MATCH("ZZZZ",A:A)))) This will look for the last numeric or last text value in column A and return the cell reference where found. Some people use the largest number Excel can handle (9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for text, but I have usually found the above to meet my needs. Regards Roger Govier wrote: Hi TemplateBuilder, Thank you for your solution but that is not exactly what I am after ... Imagine you have only 5 values filled in Column A ...bu the 5th is at row 125 then I would like it to return 125 and not 5 (or 6). Brgds Sige |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
How to find which column or row the Min() value is coming from | Excel Discussion (Misc queries) | |||
how do i find out what is uncommon in column list A1: A10 and B1:. | Excel Discussion (Misc queries) |