=LARGE problems

A

Adam

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
 
G

Gary''s Student

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$100),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.
 
T

T. Valko

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)
 
Top