Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
Have ONE column of a little more then 1,000 dates, each in a different
cell. Column is NOT in date order, nor can it be sorted. Need to to know the first CELL LOCATION of a date, and then the last CELL LOCATION of a date all in ONE (same) column. Average 100 cells between first cell location of date and last cell location. Thanx. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
e1 has the date
=MATCH(e1,D:D,0) for 1st =MATCH(e1,D:D,1) for last On Nov 3, 12:12*pm, JumboShrimps wrote: Have ONE column of a little more then 1,000 dates, each in a different cell. Column is NOT in date order, nor can it be sorted. Need to to know the first CELL LOCATION of a date, and then the last CELL LOCATION of a date all in ONE (same) column. Average 100 cells between first cell location of date and last cell location. Thanx. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
On Nov 3, 11:12*am, JumboShrimps wrote:
Have ONE column of a little more then 1,000 dates, each in a different cell. Column is NOT in date order, nor can it be sorted. Need to to know the first CELL LOCATION of a date, and then the last CELL LOCATION of a date all in ONE (same) column. Average 100 cells between first cell location of date and last cell location. Thanx. I'll use the following, you most likely have them elsewhere. - Dates in Column A starting in A5. - Date being looked for in cell A1. First row: A2: =MIN(IF(A5:A1004=A1,ROW(A5:A1004),"")) entered with CTRL+SHIFT +ENTER. Last row: A3: =MAX(IF(A5:A1004=A1,ROW(A5:A1004),"")) entered with CTRL+SHIFT +ENTER. If you need an actual cell location, you can go: B2: =ADDRESS(A2,COLUMN(A5)) B3: =ADDRESS(A3,COLUMN(A5)) S |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
On Thu, 3 Nov 2011 10:12:18 -0700 (PDT), JumboShrimps wrote:
Have ONE column of a little more then 1,000 dates, each in a different cell. Column is NOT in date order, nor can it be sorted. Need to to know the first CELL LOCATION of a date, and then the last CELL LOCATION of a date all in ONE (same) column. Average 100 cells between first cell location of date and last cell location. Thanx. If the dates are in column A, and the date to find is in G2, then: First Occurrence will be in row: =MATCH($G$2,$A:$A,0) Last Occurrence will be in row: =LOOKUP(2,1/($G$2=$A:$A),ROW($A:$A)) If you need the actual address, use the ADDRESS function to give: First Address: =ADDRESS(MATCH($G$2,$A:$A,0),1) Last Address: =ADDRESS(LOOKUP(2,1/($G$2=$A:$A),ROW($A:$A)),1) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
On Thu, 3 Nov 2011 10:52:30 -0700 (PDT), Don Guillett wrote:
=MATCH(e1,D:D,1) for last With an unsorted list I get #N/A for this one. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
On Nov 3, 3:19*pm, Ron Rosenfeld wrote:
On Thu, 3 Nov 2011 10:52:30 -0700 (PDT), Don Guillett wrote: =MATCH(e1,D:D,1) for last With an unsorted list I get #N/A for this one. missed the sorting. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
hi JumboShrimps,
for the last cell location that is an array formula to validate with ctrl+maj+enter =ADDRESS(MAX(IF(A1:A500<"",ROW(A1:A500))),MAX(IF( A1:A500<"",COLUMN(A1:A500)))) -- isabelle Le 2011-11-03 13:12, JumboShrimps a écrit : Have ONE column of a little more then 1,000 dates, each in a different cell. Column is NOT in date order, nor can it be sorted. Need to to know the first CELL LOCATION of a date, and then the last CELL LOCATION of a date all in ONE (same) column. Average 100 cells between first cell location of date and last cell location. Thanx. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First, Last occurance of date in column
for the last row location,
=ROW(INDIRECT(ADDRESS(MAX(IF($A$1:$A$500<"",ROW($ A$1:$A$500))),MAX(IF($A$1:$A$500<"",COLUMN($A$1:$ A$500)))))) still validate with Ctrl + Shift + Enter -- isabelle Le 2011-11-04 03:13, isabelle a écrit : hi JumboShrimps, for the last cell location that is an array formula to validate with ctrl+maj+enter =ADDRESS(MAX(IF(A1:A500<"",ROW(A1:A500))),MAX(IF( A1:A500<"",COLUMN(A1:A500)))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Every Occurance matching one Row and One Column | Excel Discussion (Misc queries) | |||
Percentage of the occurance of a word in a date range | Excel Worksheet Functions | |||
Count occurance of a month in a column of dates? | Excel Worksheet Functions | |||
Countinge the occurance of numbers in a column | New Users to Excel | |||
Can I divide a column at the first "space" occurance | Excel Worksheet Functions |