Solved

Excel Formula or VBA Script

Posted on 2014-07-17
270 Views
I have a large file with over 65000 rows.  For each row, if there is a "Y" in any cell of columns E, F, G, H, I would like to add a a value of "OK" in column J.

Do you know of a formula that will do this?
SAMPLE-EDEL.xlsx
0
Question by:ArisaAnsar

LVL 13

Assisted Solution

At J2 put
=IF(COUNTIF(E2:I2,"Y")>0,"OK","")

and drag the cell down to the row you want

Duncan
0

LVL 1

Accepted Solution

This should do it....

=IF((COUNTIF(E1:I1,"Y")),"OK","Not OK")
0

LVL 13

Assisted Solution

on Excel cell J2

you can do this =IF(COUNTIF(E2:I2,"Y")>0,"OK","")

on VBA,

you can do this

`````` Sub test()
Range("j2").Value = "=if(countif(e2:i2," & """Y""" & ")>0,""OK"","""")"
Range("j2").Select
Selection.AutoFill Destination:=Range("j2:j6000")
End Sub
``````
0

Author Closing Comment

Thank you!  All of this worked great.  I really appreciate it.
0

LVL 13

Expert Comment

Have a nice day

Duncan
0

Featured Post

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.