Last value in an unsorted table.

[Catalin]

1. I have a table of around 3000 rows, incresing daily.
How can I find the last entered value 'X' in column A.
Table is not sorted by column A and the value 'X' can (and
will) appear several times in this table on several
position in column A only.
Please I need urgent help.

2. How can I find all values 'X' on this table.

Thanks, Catalin

 

[Jason Morin]

With "X" in D1:

=MAX(IF(A1:A3500=D1,ROW(A1:A3500)))

Array-entered (press ctrl/shift/enter).

HTH
Jason
Atlanta, GA

 

[Vasant Nanavati]

Hi Catalin:

1. This is not very elegant. It also requires that you know the top row of
the table (the header row if there is a header) and that there are no blanks
in column A between the top or header row and the last row, and also that
there are no entries in column A below the last row of the table. If row 3
is the top (or header) row:

=OFFSET(A3,65536-COUNTBLANK(A:A)-1,0)

will give you the last entry in column A.

2. Use conditional formatting on column A. Highlight the column, with cell
A1 active. Then use Format | Conditional Formatting | Formula Is |
=A1=OFFSET(A$3,65536-COUNTBLANK(A:A)-1,0) and choose a color. This will
color all the cells that match the last entry in the column.

Regards,

Vasant.

 

[Peo Sjoblom]

1.

=MAX((A1:A500="x")*(ROW(A1:A500)))

entered with ctrl + shift & enter (adapt to fit you range)

will return the row number, if you need the cell address

=CELL("address",INDEX(A1:A500,MAX((A1:A500="x")*(ROW(A1:A500)))))

entered the same way

2.

Use data>autofilter and filter on "x"