2 queries: Display last cell, and compare two lists
 

I have two items I would like some help on, to try and create suitable
functions.

1. I have data in a worksheet (10 columns and approx 5000 rows). I would
like to be able to find the last cell with valid data (this is not always row
5000), and then display that cell contents, which will be in the format of a
time, in a cell in another worksheet.

2. In the same worksheet, I have a column containing approx 5000 alarm
entries, and which is typically multiple occurences of around 10 different
alarm types. What I would like to do, is be able to compare that data against
a list of defined alarm types (conatined in either a list or range of cells)
and if possible display any exceptions, which are not in the known list, in
another cell.

If possible I would like to avoid use of VB
Thanks in advance

Hi!

Question 1:

Sounds like you want to find the last numeric value ("which will be in the
format of a time") in a column:

=LOOKUP(9.9999999999E+307,Sheet1!A:A)

Returns the last numeric value from Sheet1 column A. You'll have to format
the cell as TIME.

Question 2:

You want to "search" a list of 5000 for entries that do not appear in
another list?

If as you say: "which is typically multiple occurences of around 10
different alarm types.", that means there are only 10 unique entries, so why
not just extract the 10 uniques and compare those against the the other
list?

I would use an advanced filter to copy the uniques to another location then
use this formula to extract those entries that do not appear in the other
list:

Assume uniques are extracted to A1:A10

The list of defined alarm types is in B1:B10

Entered as an array using the key combo of CTRL,SHIFt,ENTER:

=IF(ISERROR(SMALL(IF(COUNTIF(B$1:B$10,A$1:A$10)=0, (ROW($1:$10)-ROW(A$1))+1),ROW(1:1))),"",INDEX(A$1:A$10,SMALL(IF (COUNTIF(B$1:B$10,A$1:A$10)=0,(ROW($1:$10)-ROW(A$1))+1),ROW(1:1))))

Copy down until you get blanks. That will extract entries in column A that
do not appear in column B.

Biff

"Thierry" wrote in message
...
I have two items I would like some help on, to try and create suitable
functions.

1. I have data in a worksheet (10 columns and approx 5000 rows). I would
like to be able to find the last cell with valid data (this is not always
row
5000), and then display that cell contents, which will be in the format of
a
time, in a cell in another worksheet.

2. In the same worksheet, I have a column containing approx 5000 alarm
entries, and which is typically multiple occurences of around 10 different
alarm types. What I would like to do, is be able to compare that data
against
a list of defined alarm types (conatined in either a list or range of
cells)
and if possible display any exceptions, which are not in the known list,
in
another cell.

If possible I would like to avoid use of VB
Thanks in advance