First, Last occurance of date in column

  • Thread starter Thread starter JumboShrimps
  • Start date Start date
J

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.
 
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.
Click to expand...

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
 
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 :
 
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
 
Back
Top