Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the first or last populated column in a range
I have a range of data, from columns C through Z.
And I have about 100 rows of financial data. The rows represent projects, and the columns represents periods where costs were incurred. So, some rows have data from C-Z, and others have data from H-M. How can I write a formula in column a1, to tell me which was the first column to have a value in row 1? And, likewise, I want to know which column has the last value. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the first or last populated column in a range
Commish wrote:
I have a range of data, from columns C through Z. And I have about 100 rows of financial data. The rows represent projects, and the columns represents periods where costs were incurred. So, some rows have data from C-Z, and others have data from H-M. How can I write a formula in column a1, to tell me which was the first column to have a value in row 1? And, likewise, I want to know which column has the last value. Array formula in A1 (commit with CTRL+SHIFT+ENTER): =MIN(IF($C1:V1="","",COLUMN($C1:V1))) Use MAX() for B1. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the first or last populated column in a range
What type of data is in the cells? Text? Numbers? Both? Any formulas that
return formula blanks? Exactly what result do you want? The column letter? -- Biff Microsoft Excel MVP "Commish" wrote in message ... I have a range of data, from columns C through Z. And I have about 100 rows of financial data. The rows represent projects, and the columns represents periods where costs were incurred. So, some rows have data from C-Z, and others have data from H-M. How can I write a formula in column a1, to tell me which was the first column to have a value in row 1? And, likewise, I want to know which column has the last value. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the first or last populated column in a range
Commish wrote...
.... How can I write a formula in column a1, to tell me which was the first column to have a value in row 1? And, likewise, I want to know which column has the last value. Most efficient ways First column [array formula]: =MATCH(1,1-ISBLANK(C1:Z1),0) Last column [array formula]: =MATCH(2,1/(1-ISBLANK(C1:Z1))) If you want to treat cells evaluating to "" the same as blank (empty) cells, First column [array formula]: =MATCH(TRUE,C1:Z1<"",0) Last column [array formula]: =MATCH(2,1/(C1:Z1<"")) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the first or last populated column in a range
On May 13, 4:42*pm, "T. Valko" wrote:
What type of data is in the cells? Text? Numbers? Both? Any formulas that return formula blanks? Exactly what result do you want? The column letter? -- Biff Microsoft Excel MVP "Commish" wrote in message ... I have a range of data, from columns C through Z. And I have about 100 rows of financial data. The rows represent projects, and the columns represents periods where costs were incurred. So, some rows have data from C-Z, and others have data from H-M. How can I write a formula in column a1, to tell me which was the first column to have a value in row 1? And, likewise, I want to know which column has the last value. Biff, the data is either numeric, or empty. The column letter is fine, but ideally I'd want the value in a specific row on the answer column. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the first or last populated column in a range
Ok, in other words, you want to return the corresponding column headers for
the first numeric entry and last numeric entry in a row. Assume column headers are in the range C1:Z1 Numeric data in the range C2:Z2 (includes empty cells) For the first numeric entry, array entered** : =IF(COUNT(C2:Z2),INDEX(C$1:Z$1,MATCH(TRUE,ISNUMBER (C2:Z2),0)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the last numeric entry: =IF(COUNT(C2:Z2),INDEX(C$1:Z$1,MATCH(1E100,C2:Z2)) ,"") If the column headers are dates, format the formula results as Date. -- Biff Microsoft Excel MVP "Commish" wrote in message ... On May 13, 4:42 pm, "T. Valko" wrote: What type of data is in the cells? Text? Numbers? Both? Any formulas that return formula blanks? Exactly what result do you want? The column letter? -- Biff Microsoft Excel MVP "Commish" wrote in message ... I have a range of data, from columns C through Z. And I have about 100 rows of financial data. The rows represent projects, and the columns represents periods where costs were incurred. So, some rows have data from C-Z, and others have data from H-M. How can I write a formula in column a1, to tell me which was the first column to have a value in row 1? And, likewise, I want to know which column has the last value. Biff, the data is either numeric, or empty. The column letter is fine, but ideally I'd want the value in a specific row on the answer column. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the first or last populated column in a range
"Commish" wrote in message
... I have a range of data, from columns C through Z. And I have about 100 rows of financial data. The rows represent projects, and the columns represents periods where costs were incurred. So, some rows have data from C-Z, and others have data from H-M. How can I write a formula in column a1, to tell me which was the first column to have a value in row 1? And, likewise, I want to know which column has the last value. On May 13, 4:42 pm, "T. Valko" wrote: What type of data is in the cells? Text? Numbers? Both? Any formulas that return formula blanks? Exactly what result do you want? The column letter? -- Biff Microsoft Excel MVP Commish wrote: Biff, the data is either numeric, or empty. The column letter is fine, but ideally I'd want the value in a specific row on the answer column. Which row? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to display last entry in a column populated with #N/As | Excel Worksheet Functions | |||
Last populated cell in a column | Excel Discussion (Misc queries) | |||
Last populated cell in a column | Excel Discussion (Misc queries) | |||
LookUp to Display Last Value Populated a in Range | Excel Worksheet Functions | |||
Referencing last populated cell in a column | Excel Worksheet Functions |