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! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
