Harvesting non-blank rows from a sheet

[Nine Tail Fox]

Hi everyone! I have an Excel query that I can't quite wrap my head around, so I'm hoping to get some advice. I'm working in Excel 2003.

I have a simple weekly rota for staff which has columns of data roughly as follows:

(A1) Name | (B1) Mon Start Time | (C1) Mon Finish Time | (D1) Tue Start Time | (E1) Tue Finish Time | (F1) Wed Start Time | (G1) Wed Finish Time ... (etc.)
(A2) J. Smith | (B2) 08:00 | (C2) 17:00 | (D2) 09:00 | (E2) 18:00 | (F2 blank) | (G2 blank) ... (etc.)

So for the days where they work each staff member has their start and finish time entered; for days where they don't, the cell is left blank.

What I need to do is, on a separate worksheet, output a list of names and start/finish times for just the staff that work on a particular day. So for example for Monday I need to find the rows with non-blank cells in column B, then output the values of cells A, B and C for each of those rows.

So let's say I had five staff with shifts on Monday, the names and start/finish times for those five staff would appear in rows 1-5 of my new worksheet, and the names and start/finish times for other staff from the main rota who didn't have shifts on Monday would not appear.

Hope I've explained this in a way that makes sense.

Any thoughts or advice with this would be very much appreciated! Thanks very much in advance.

[dilipandey]

Hi Nine Tail Fox,


Welcome to the forum.

Suggest you to upload an sample workbook to support your query.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>