You can use the following function as a solution. There might be better
ones.
1. Open VBA editor ; Insert a module and then copy-paste the following code.
'-----------------------------------------------------
Option Explicit
Function lastWord(r As Range, sWord As String)
Dim i As Integer, j As Integer
For i = 1 To r.Rows.Count
If r.Cells(i, 2) = sWord Then
j = i
Do While r.Cells(j, 2) = sWord
j = j + 1
Loop
j = j - 1
Exit For
End If
Next i
lastWord = r.Cells(j, 1)
End Function
'------------------------------------------------------
2. Let's say your data is in range A1:B11. Put the following formula in any
cell to extract the dates for 'Alarm' and 'Reset' respectively.
=lastWord(A1:B11,"ALARM")
=lastWord(A1:B11,"HEALTHY")
Hope it solves your problem.
On Thu, Sep 18, 2008 at 8:52 PM, StraightEight <[EMAIL PROTECTED]>wrote:
>
> I am wondering if anyone can help me summarise some data. I have a
> sheet which takes external data from a control system and reports an
> alarm status...and I'd like to somehow summarise this on another sheet
> so I can see just the times of the last time we went into alarm, and
> the last time we came out.
>
> eg.
>
> 18-Sep-08 12:42:17 ALARM
> 18-Sep-08 10:41:17 ALARM
> 18-Sep-08 03:42:17 HEALTHY
> 17-Sep-08 21:48:16 ALARM
> 17-Sep-08 21:47:16 HEALTHY
> 17-Sep-08 21:46:16 ALARM
> 17-Sep-08 14:48:16 HEALTHY
> 17-Sep-08 09:40:16 ALARM
> 17-Sep-08 02:40:16 HEALTHY
> 16-Sep-08 21:42:15 ALARM
> 16-Sep-08 14:43:15 HEALTHY
>
> I added two formulas to the right of these columns which checks to see
> whether or not the previous value was different, and if so, it writes
> a 1, so i end up with this
>
> 18-Sep-08 12:42:17 ALARM 0 0
> 18-Sep-08 10:41:17 ALARM 1 0
> 18-Sep-08 03:42:17 HEALTHY 0 1
> 17-Sep-08 21:48:16 ALARM 1 0
> 17-Sep-08 21:47:16 HEALTHY 0 1
> 17-Sep-08 21:46:16 ALARM 1 0
> 17-Sep-08 14:48:16 HEALTHY 0 1
> 17-Sep-08 09:40:16 ALARM 1 0
> 17-Sep-08 02:40:16 HEALTHY 0 1
> 16-Sep-08 21:42:15 ALARM 1 0
> 16-Sep-08 14:43:15 HEALTHY 0 0
>
> So what I need is the timestamp for the first 1 from each column to go
> on another sheet, so I would see this.
>
>
> LAST ALARM: 18-Sep-08 10:41:17 LAST RESET: 18-Sep-08 03:42:17
>
> I'm sure this can probably be done very easily with some sort of
> VLOOKUP but I am unsure how you specifically just look up that first
> instance of 1 in the column. I'd also like to be able to automate this
> as much as possible as the external data is updated as soon as I open
> the sheet so ideally it would be great if I had a button I could click
> that would update the summary sheet. Any ideas? Many many thanks in
> advance!
>
> >
>
--
Akhilesh Kumar Karna
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
