Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare two Excel worksheets for cell differences? | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
Formula to identify cell with different value in list | Excel Worksheet Functions | |||
Function syntax to compare cell contents | Excel Worksheet Functions | |||
Compare cell values | Excel Worksheet Functions |