Copy and Paste Rows Based On Time

[Ethro]

Hi guys, I have a sheet with over 1000 times. One coloum is sign in time, and one is sign off time. I want to copy the rows of data to another sheet (doesnt matter about the name of the new sheet, i can call it whatever) whos time is greater then 2:30.

Preferably not a Macro as im new to them, but if it has to be then I guess i can learn.

The column with the times that im concerned about is Column F, and the row spans from Column A to Column F.

Thanks

 

[Derek Brown]

Hello and welcome to The Board.
Here is a solution using formulas. It makes use of two 'helper' columns (one on each sheet) but these can be hidden. I will use "Sheet1" as the sheet containing your data and "Sheet2" as the new (target) worksheet. Obviously you can change them as required.

On Sheet1 (your existing data):
Add a new Column G as follows:

G1 - enter the time that you want to test against (in this example 2:30). I suggest making it 'bold' so that it stands out from the rest of the column.

G2 - enter the formula =IF(F2>$G$1,1,0)
This assumes that row 1 has headings.

Copy-down the formula in G2 to the other rows in Sheet1. You will now have the value 1 for the rows that meet your criteria and 0 (zero) where they do not.

Note: for the example that now follows, I use only a small dataset of 3 rows of data with 1 heading row. So formulas such as: "INDEX(Sheet1!$A$2:$F$4, ......" and "COUNT(Sheet1!$A$2:$A$4)" will need to be adjusted accordingly.

On Sheet2:
Copy your headings from Sheet1 A1:F1 to Sheet2 B1:G1 (that is, leaving Column A blank).

A1 - enter the value 1 (this is what you will be matching on in Column G on Sheet1). I suggest making it 'bold' so that it stands out from the rest of the column.

A2 - Enter the formula:
=IF(ISERROR(MATCH($A$1,OFFSET(Sheet1!$G$2,0,$A$1-1,COUNT(Sheet1!$A$2:$A$4),1),0)),
"",MATCH($A$1,OFFSET(Sheet1!$G$2,0,$A$1-1,COUNT(Sheet1!$A$2:$A$4),1),0))

The above formula as been written for Excel 2003 and earlier (it works for Excel 2007 but you could use IFERROR instead of "IF(ISERROR").

A3 - Enter the formula:
=IF(ISERROR(MATCH($A$1,OFFSET(Sheet1!$G$2,A2,0,COUNT(Sheet1!$A$2:$A$4)-A2,1),0)+A2),
"",MATCH($A$1,OFFSET(Sheet1!$G$2,A2,0,COUNT(Sheet1!$A$2:$A$4)-A2,1),0)+A2)

Copy-down the A3 formula as far as you need to match the rows on Sheet1.

B2 - Enter the formula:
=IF(ISERROR(INDEX(Sheet1!$A$2:$F$4,$A2,COLUMN()-1)),
"",INDEX(Sheet1!$A$2:$F$4,$A2,COLUMN()-1))

Copy the formula in B2 to columns C2:G2 and then down for a many rows that you have in Column A.

And that's it - it looks complicated but if you follow the example step by step it will not be difficult to set up.

If you have any blank cells in your dataset, you will probably get the value 0 (zero) on Sheet2. To overcome this, you will need to test for the cell having zero length. You will need to format Sheet2 columns to match those on Sheet1.

 

[Ethro]

Excellent! Ill give this a try tomorrow when I'm back in the office.

Thanks!

 

[Ethro]

From here down I could not get it to work. Im using Excell 2000. It doesn't say whats wrong,It just comes up with the standard error with a formula box.

Do you have an example workbook I could maybe work from/adapt for my needs?

Thanks again, appreciate the time taken to write it out/test it.

A2 - Enter the formula:
=IF(ISERROR(MATCH($A$1,OFFSET(Sheet1!$G$2,0,$A$1-1,COUNT(Sheet1!$A$2:$A$4),1),0)),
"",MATCH($A$1,OFFSET(Sheet1!$G$2,0,$A$1-1,COUNT(Sheet1!$A$2:$A$4),1),0))

The above formula as been written for Excel 2003 and earlier (it works for Excel 2007 but you could use IFERROR instead of "IF(ISERROR").

A3 - Enter the formula:
=IF(ISERROR(MATCH($A$1,OFFSET(Sheet1!$G$2,A2,0,COUNT(Sheet1!$A$2:$A$4)-A2,1),0)+A2),
"",MATCH($A$1,OFFSET(Sheet1!$G$2,A2,0,COUNT(Sheet1!$A$2:$A$4)-A2,1),0)+A2)

Copy-down the A3 formula as far as you need to match the rows on Sheet1.

B2 - Enter the formula:
=IF(ISERROR(INDEX(Sheet1!$A$2:$F$4,$A2,COLUMN()-1)),
"",INDEX(Sheet1!$A$2:$F$4,$A2,COLUMN()-1))

Copy the formula in B2 to columns C2:G2 and then down for a many rows that you have in Column A.

And that's it - it looks complicated but if you follow the example step by step it will not be difficult to set up.

If you have any blank cells in your dataset, you will probably get the value 0 (zero) on Sheet2. To overcome this, you will need to test for the cell having zero length. You will need to format Sheet2 columns to match those on Sheet1.

 

[Derek Brown]

I have had a quick look through some of my books and, as far as I can tell, the functions are available in Excel 2000.
Probably the first thing to do is use the Formula Auditing toolbar to try to trace the cause of the error.
Did you enter the formula from the keyboard or did you copy/paste it from this page? Copy/paste would ensure that you have no error on entry but make sure that the worksheet names and the rows/columns used are the same as in the example.