=LARGE problems
 

I would like to move just the latest ten dates that appear in a column (H)
into a smaller table on a different sheet. I have been using =LARGE but
there are repetitions of the same date throughout H on the bigger table.

Is this possible, can anyone advise?

Thanks

=LARGE problems
 

If you want the latest 10 dates, but without any repeats, then an easy was is
to use a helper column that removes the duplicates from column H. Then you
can use your LARGE() function on the helper column.

In I1 enter:
=H1

In I2 enter (AS AN ARRAY FORMULA):

=IF(ISERROR(MATCH(0,COUNTIF(I$1:I1,$H$1:$H$100&"") ,0)),"",INDEX(IF(ISBLANK($H$1:$H$100),"",$H$1:$H$1 00),MATCH(0,COUNTIF(I$1:I1,$H$1:$H$100&""),0)))

and copy down

then the series of formulas like:

=LARGE(I1:I100,1)
=LARGE(I1:I100,2)
=LARGE(I1:I100,3)

will have no duplicates. REMEMBER:

an array formula.....enter with CNTRL-SHFT-ENTER, not just the ENTER key.
--
Gary''s Student - gsnu200777


"Adam" wrote:

I would like to move just the latest ten dates that appear in a column (H)
into a smaller table on a different sheet. I have been using =LARGE but
there are repetitions of the same date throughout H on the bigger table.

Is this possible, can anyone advise?

Thanks

=LARGE problems
 

One way...

Enter this formula in A1:

=MAX(H1:H100)

Enter this array formula** in A2 and copy down to A10:

=MAX(IF(H$1:H$100<A1,H$1:H$100))

Format as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Adam" wrote in message
...
I would like to move just the latest ten dates that appear in a column (H)
into a smaller table on a different sheet. I have been using =LARGE but
there are repetitions of the same date throughout H on the bigger table.

Is this possible, can anyone advise?

Thanks