Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Formula or VBA Script

Posted on 2014-07-17
5
Medium Priority
?
297 Views
Last Modified: 2014-07-17
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
Comment
Question by:ArisaAnsar
  • 3
5 Comments
 
LVL 13

Assisted Solution

by:duncanb7
duncanb7 earned 1332 total points
ID: 40203221
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

by:
T.J. Couey earned 668 total points
ID: 40203224
This should do it....

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

Assisted Solution

by:duncanb7
duncanb7 earned 1332 total points
ID: 40203309
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

Open in new window

0
 

Author Closing Comment

by:ArisaAnsar
ID: 40203368
Thank you!  All of this worked great.  I really appreciate it.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40203384
Thanks for your points

Have a nice day

Duncan
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question