Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First populated cell in row array/ Last populated cell in row arra
Hello!
I have data in a single row array. The data is not contiguous such that there are holes in the data. Several cells may be blank in the first few cells, in the middle cells, or in the last cells. I need to identify which cell is the one where the data begins. In addition, I need to identify which cell is the one where the data ends. It is not necessary to locate "holes" in the data set. I would like to use functions only to identify these first and last cells. Thank you very much!! Tom |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First populated cell in row array/ Last populated cell in row arra
Skyscan,
Array enter each of these (enter using Ctrl-Shift-Enter) First filled cell of row 2: =ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) ) Last filled cell of row 2: =ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0))) HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Hello! I have data in a single row array. The data is not contiguous such that there are holes in the data. Several cells may be blank in the first few cells, in the middle cells, or in the last cells. I need to identify which cell is the one where the data begins. In addition, I need to identify which cell is the one where the data ends. It is not necessary to locate "holes" in the data set. I would like to use functions only to identify these first and last cells. Thank you very much!! Tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First populated cell in row array/ Last populated cell in row
Thank you very much!!
Tom "Bernie Deitrick" wrote: Skyscan, Array enter each of these (enter using Ctrl-Shift-Enter) First filled cell of row 2: =ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) ) Last filled cell of row 2: =ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0))) HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Hello! I have data in a single row array. The data is not contiguous such that there are holes in the data. Several cells may be blank in the first few cells, in the middle cells, or in the last cells. I need to identify which cell is the one where the data begins. In addition, I need to identify which cell is the one where the data ends. It is not necessary to locate "holes" in the data set. I would like to use functions only to identify these first and last cells. Thank you very much!! Tom |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First populated cell in row array/ Last populated cell in row
Sorry, but I was not able to get it to work. Please explain the comment
about array entering using Cntrl-Shift-Enter. Thanks! Tom "Skyscan" wrote: Thank you very much!! Tom "Bernie Deitrick" wrote: Skyscan, Array enter each of these (enter using Ctrl-Shift-Enter) First filled cell of row 2: =ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) ) Last filled cell of row 2: =ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0))) HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Hello! I have data in a single row array. The data is not contiguous such that there are holes in the data. Several cells may be blank in the first few cells, in the middle cells, or in the last cells. I need to identify which cell is the one where the data begins. In addition, I need to identify which cell is the one where the data ends. It is not necessary to locate "holes" in the data set. I would like to use functions only to identify these first and last cells. Thank you very much!! Tom |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First populated cell in row array/ Last populated cell in row
Tom,
Instead of typing in the formula and pressing <Enter, you need to type the formula in, hold down the <Ctrl key, hold down the <Shift key, and while those two keys are held down, press the <Enter key. This enters the formula as an array formula, which is a special formula type that evaluates each cell in the range on a cell by cell basis, rather than all at once. If you cannot get the formulas to work, I will send you a sample workbook with the formulas working, if you post your email address or contact me privately. HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Sorry, but I was not able to get it to work. Please explain the comment about array entering using Cntrl-Shift-Enter. Thanks! Tom "Skyscan" wrote: Thank you very much!! Tom "Bernie Deitrick" wrote: Skyscan, Array enter each of these (enter using Ctrl-Shift-Enter) First filled cell of row 2: =ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) ) Last filled cell of row 2: =ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0))) HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Hello! I have data in a single row array. The data is not contiguous such that there are holes in the data. Several cells may be blank in the first few cells, in the middle cells, or in the last cells. I need to identify which cell is the one where the data begins. In addition, I need to identify which cell is the one where the data ends. It is not necessary to locate "holes" in the data set. I would like to use functions only to identify these first and last cells. Thank you very much!! Tom |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First populated cell in row array/ Last populated cell in row
Thanks Bernie. I believe I did as you stated. I tried experimenting with
the position of the numbers in row 2. The result of the function shows either $A$2 or $IV$2. When I go to Insert, Function and I look at the template box ... I see the result is correct- it shows the location of the cell where the data begins. Why would it only show correctly there, but not as a result in the cell? I'm sorry to ask another question here, but is there a function I can use to find the contents of the first populated cell in that row? Thanks! Tom "Bernie Deitrick" wrote: Tom, Instead of typing in the formula and pressing <Enter, you need to type the formula in, hold down the <Ctrl key, hold down the <Shift key, and while those two keys are held down, press the <Enter key. This enters the formula as an array formula, which is a special formula type that evaluates each cell in the range on a cell by cell basis, rather than all at once. If you cannot get the formulas to work, I will send you a sample workbook with the formulas working, if you post your email address or contact me privately. HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Sorry, but I was not able to get it to work. Please explain the comment about array entering using Cntrl-Shift-Enter. Thanks! Tom "Skyscan" wrote: Thank you very much!! Tom "Bernie Deitrick" wrote: Skyscan, Array enter each of these (enter using Ctrl-Shift-Enter) First filled cell of row 2: =ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) ) Last filled cell of row 2: =ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0))) HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Hello! I have data in a single row array. The data is not contiguous such that there are holes in the data. Several cells may be blank in the first few cells, in the middle cells, or in the last cells. I need to identify which cell is the one where the data begins. In addition, I need to identify which cell is the one where the data ends. It is not necessary to locate "holes" in the data set. I would like to use functions only to identify these first and last cells. Thank you very much!! Tom |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First populated cell in row array/ Last populated cell in row
Tom,
If your row is entirely blank, the first function will return $IV$2, and the second one will return a #VALUE! error. But I don't know why you are getting the results that you show. Are the cells truly blank, or do they have formulas in them? Try selecting the entire row, pressing Delete, then typing something into one cell of that row. Both formulas should return the same address. Contact me a deitbe at consumer dot org and I will send you a working example, or email me a copy of your workbook and I will take a look at it. HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Thanks Bernie. I believe I did as you stated. I tried experimenting with the position of the numbers in row 2. The result of the function shows either $A$2 or $IV$2. When I go to Insert, Function and I look at the template box ... I see the result is correct- it shows the location of the cell where the data begins. Why would it only show correctly there, but not as a result in the cell? I'm sorry to ask another question here, but is there a function I can use to find the contents of the first populated cell in that row? Thanks! Tom "Bernie Deitrick" wrote: Tom, Instead of typing in the formula and pressing <Enter, you need to type the formula in, hold down the <Ctrl key, hold down the <Shift key, and while those two keys are held down, press the <Enter key. This enters the formula as an array formula, which is a special formula type that evaluates each cell in the range on a cell by cell basis, rather than all at once. If you cannot get the formulas to work, I will send you a sample workbook with the formulas working, if you post your email address or contact me privately. HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Sorry, but I was not able to get it to work. Please explain the comment about array entering using Cntrl-Shift-Enter. Thanks! Tom "Skyscan" wrote: Thank you very much!! Tom "Bernie Deitrick" wrote: Skyscan, Array enter each of these (enter using Ctrl-Shift-Enter) First filled cell of row 2: =ADDRESS(ROW(2:2),MIN(IF(2:2<"",COLUMN(2:2),256)) ) Last filled cell of row 2: =ADDRESS(ROW(2:2),MAX(IF(2:2<"",COLUMN(2:2),0))) HTH, Bernie MS Excel MVP "Skyscan" wrote in message ... Hello! I have data in a single row array. The data is not contiguous such that there are holes in the data. Several cells may be blank in the first few cells, in the middle cells, or in the last cells. I need to identify which cell is the one where the data begins. In addition, I need to identify which cell is the one where the data ends. It is not necessary to locate "holes" in the data set. I would like to use functions only to identify these first and last cells. Thank you very much!! Tom |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Request
Hi
I am trying on a worksheet where I need to do the following: first, at the end of the worksheet entries, display the sum of each category and this should be automated: example: if column XYZ contains - a,a,b,c,d,a,b,c,d expected result should be: Total: a - 3 b - 2 c - 2 d - 2 Second, automatically calculate the entries in a column with number, which are not continuous example: if column 123 contains - 1,2, ,3, , , 4, 1 Expected result should be: Total: 11 I need to automate the above two and for this, I need to identify the first and last rows in the sheet to use in the formula. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing last populated cell in a column | Excel Worksheet Functions | |||
formula for a cell which is blank returns populated cell | Excel Discussion (Misc queries) | |||
How do I hide a row in excel if a certain other cell is populated | Excel Worksheet Functions | |||
Index heading when cell is populated | Excel Worksheet Functions | |||
Check to see if a cell is populated | Excel Worksheet Functions |