First, Last occurance of date in column

[JumboShrimps]

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.

 

[Don Guillett]

e1 has the date
=MATCH(e1,D,0)
for 1st
=MATCH(e1,D,1)
for last

 

[Scott]

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

 

[Don Guillett]

With an unsorted list I get #N/A for this one.

missed the sorting.

 

[isabelle]

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 :

 

[isabelle]

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