# Thread: logic test for copying data

1. Hi To All,

I've been searching for a formula that will check for data that fails a test for example data that is 0.5< and >20.5, such that the data that fails the test or is out of spec is copied, not moved to an adjacent column on the same worksheet. Thanks to the helpful folks here I get the first part, evaluating the data. But I cannot solve the copy to another column half of the formula.

any suggestions?

Thank you...

Marty

2. Try this

=IF(OR(A1<0.5,A1>20.5)=TRUE,A1,"")

3. Let's say you have data in column A, starting in cell A2.
In another column, in Row 2, enter this formula:

=IF(OR(A2<0.5,A2>20.5),A2,"")

Fill down as far as needed.
Does that do what you want?

4. Hi,

I should have been a little more detailed, I've attached a section of the worksheet problem. From the sample i need to evaluate the data in column "d" ex. d4<0.5,d4>20.5, any data that do not meet this test must be copied to an adjacent column such as column f or g. Column f or g would contain in the (same row number) only the actual data that failed the above test. Hope this is a little clearer.

Thank you..

Marty

5. I don't see an attachment?

Select the column/row of data you need changing
Code:
```Sub MoveMe()
Dim intCount
intCount = Selection.Rows.Count

For i = 2 To intCount + 1

Select Case Cells(i, 4).Value

Case Is < 0.5
Cells(i, 6).Value = Cells(i, 4).Value
Case Is > 20.5
Cells(i, 6).Value = Cells(i, 4).Value

End Select

Next i
End Sub```

7. If Jerry's code provides the reverse of what you want, try the following
Code:
```Sub MoveMe()
Dim intCount

intCount = Selection.Rows.Count
For i = 2 To intCount + 1

If Cells(i, 4).Value >= 0.5 _
And Cells(i, 4).Value <= 20.5 _
Then Cells(i, 6).Value = Cells(i, 4).Value
Next i
End Sub```

8. Well funny how it doesn't work unless one hits the "upload file" icon...sorry. Here it is.

Regards,
Marty

9. Originally Posted by mojave1
Well funny how it doesn't work unless one hits the "upload file" icon...sorry. Here it is.

Regards,
Marty
Hi Marty

=IF(OR(D2<0.5,D2>20.5),K2,"") the formula you have in F2 should have been =IF(OR(D2<0.5,D2>20.5),D2,"") and of course filled down to the other cells in column F.

10. The formula =IF(OR(D3<0.5,D3>20.5),D3,"") will work properly if you put the formula in Column F or column G.

This will put only the values that are not within the range specified in the column containing the formula.

Bob

11. [attachment=87217:Wopr Sample.xls]
Try this.
You'll notice I've entered your minimum/maximum values in the spreadsheet and then referenced the cells containing those values.
This is often a more useful way to construct these kinds of formulas, since then you can test against new values without having to rebuild your formulas.

I also added some conditional formatting to highlight the rows that failed the test.

12. Thank you for your responses, Catharine what does the ),D23,"") portion do? How is the copying of the data to the adjacent column performed, and how would this formula be different if the data where copied to say cloumn M?

Great suugestion regarding the test criteria in one location ..

Thanks,
Marty

13. If I break the formula down (and I know that you might be familiar with some of this stuff - but the detail is for the benefit of anyone else following this thread):
=IF(OR(D2<\$I\$2,D2>\$J\$2),D2,"")
The IF statement has the following syntax IF(some test here, what to do if true, what to do if false)
You notice the commas - they separate the clauses of the IF statement.

In this case, if the test value falls outside of the ranges stored in I2 and J2, then display the test value, otherwise do nothing.
The formula will work just fine if copied to column M - as long as it is referring to the values in Column D (that is what you want to test) and that the Minimum/Maximum values remain in I2 and J2. If you decide to relocate them - then obviously the formula will have to be updated too.

14. Hi Catharine,

Thanks for the detail.....I got it, and the absolute cell references make it easier to work with.

Thanks again.

Regards,
Marty

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•