Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding data between blank rows
A B
1 6:00 a 7:00 a 2 7:05 a 8:30 a 3 8:35 a 10:00 a 4 5 12:00 p 1:00 p 6 1:05 p 4:00p 7 8 6:00p 10:00p Each call-out will seperated by a blank row and, as shown above, can be made up of single or mutiple rows. There may be only 1 call-out or up to 5 in a day. I'm tring to find the start and stop times for each. Start End 9 1st 6:00a 10:00a 10 2nd 12:00p 2:00p 11 3rd 5:00p 7:00p Thanks for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding data between blank rows
Not sure I totally understand the issue, but I do, this will do what you want:
=IF(ROWS($1:1)<=COUNTA(A1:A10),INDEX(A1:A10,SMALL( IF(A1:A10<"",ROW(A1:A10)-MIN(ROW(A1:A10))+1),ROWS($1:1))),"") Commit the function with Ctrl+Shift+Enter, and then fill down until you get blanks.. Regards, Ryan--- -- RyGuy "j.ruderman" wrote: A B 1 6:00 a 7:00 a 2 7:05 a 8:30 a 3 8:35 a 10:00 a 4 5 12:00 p 1:00 p 6 1:05 p 4:00p 7 8 6:00p 10:00p Each call-out will seperated by a blank row and, as shown above, can be made up of single or mutiple rows. There may be only 1 call-out or up to 5 in a day. I'm tring to find the start and stop times for each. Start End 9 1st 6:00a 10:00a 10 2nd 12:00p 2:00p 11 3rd 5:00p 7:00p Thanks for any help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding data between blank rows
Sorry for the confusion wtih my 1st post...I was half asleep. The sheet is a
daily timesheet which is linked to a bi-weekly sheet. Each shift seperation has to be listed seperatly on the bi-weekly sheet for that corresponding day. Going off the original sample sheet of times: Start Stop Shift 1 6:00a 10:00a Shift 2 12:00p 4:00p Shift 3 6:00p 10:00p Shift 4 Shift 5 "ryguy7272" wrote: Not sure I totally understand the issue, but I do, this will do what you want: =IF(ROWS($1:1)<=COUNTA(A1:A10),INDEX(A1:A10,SMALL( IF(A1:A10<"",ROW(A1:A10)-MIN(ROW(A1:A10))+1),ROWS($1:1))),"") Commit the function with Ctrl+Shift+Enter, and then fill down until you get blanks.. Regards, Ryan--- -- RyGuy "j.ruderman" wrote: A B 1 6:00 a 7:00 a 2 7:05 a 8:30 a 3 8:35 a 10:00 a 4 5 12:00 p 1:00 p 6 1:05 p 4:00p 7 8 6:00p 10:00p Each call-out will seperated by a blank row and, as shown above, can be made up of single or mutiple rows. There may be only 1 call-out or up to 5 in a day. I'm tring to find the start and stop times for each. Start End 9 1st 6:00a 10:00a 10 2nd 12:00p 2:00p 11 3rd 5:00p 7:00p Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLANK CE | Excel Worksheet Functions | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions | |||
finding blank cell and moving specific data into it | Excel Worksheet Functions |