0

I have a large list of users with various codes. The goal is to separate the good from the bad. All codes that start with P, H or F are good. All codes that start with L or K are bad. Example:

Email             | Code 01 | Code 02 | Code 03 | Code 04 | RESULT
user01@gmail.com  | PJR-VRF |         |         |         | GOOD
user01@gmail.com  |         | LNR-JNT |         | LNR-JNT | BAD
user01@gmail.com  |         | HVB-YWQ | HVB-YWQ | HVB-YWQ | GOOD
user01@gmail.com  |         | LNR-JNT |         | KVB-MMO | BAD
user02@gmail.com  |         |         |         | PHH-KLP | GOOD
user02@gmail.com  |         | HNR-OPT |         | LNR-JNT | GOOD
user02@gmail.com  |         | FLT-MWQ |         | FLT-MWQ | GOOD
user02@gmail.com  |         | KVB-MMO | KVB-MMO | KVB-MMO | BAD

In other words, if a range contains any cells that begin with P, H, or F then good, otherwise bad. I've tried to use the following formula in the result column:

=IF(COUNTIF(B2:E2,{"H*","P*","F*"}),"good","bad")

It didn't work unfortunately, so I tried this:

=IF(COUNTIF(B2:E2,"H*") + COUNTIF(B2:E2,"P*") + COUNTIF(B2:E2,"F*"),"good","bad")

This seems to work, but is this the best method? Say I have 20 other conditions to check against? And need to add a third result? What is the best approach to sift this data?

Josh
  • 21
  • 5
  • Change L to F for good codes in this context: *if a range contains any cells that begin with P, H, or L then good* – bzimor Nov 09 '16 at 04:27
  • Sorry, that should have read, _P, H or F_. – Josh Nov 09 '16 at 04:31
  • 20 conditions: other codes beginning with other unique letters. Third result: Other. For example, another condition may be cells with codes beginning with an 'E' have a result of 'OTHER'. – Josh Nov 09 '16 at 05:39
  • If you have additional conditions to check for then the formula might just get a bit more complex. Alternatively, you could also check for additional conditions in a separate (helper-) column. If there are not that many rows then I don't see a problem with that. Yet, if you wish to optimize and wish to experiment with different methods to go about it (to do your own speed testing) then you might be better off asking the question [here at code review](http://codereview.stackexchange.com/). – Ralph Nov 09 '16 at 11:52
  • Thanks Ralph. I think you're right. Beyond this it seems to be adding more COUNTIFs and then nested IFs for other results. Which point toward using a VLOOKUP table I believe. – Josh Nov 09 '16 at 14:41

1 Answers1

0

You can use an array formula similar to this:

=IF(SUM((--($B2:$D2<>""))*(--ISNUMBER(FIND(LEFT($B2:$D2,1),"HPF"))))>0,"GOOD","BAD")

Please remember to press Ctrl+Shift+Enter to complete the array formula correctly.

If you need to add a third result:

=IF(SUM((--($B2:$D2<>""))*(--ISNUMBER(FIND(LEFT($B2:$D2,1),"HPF"))))>0,"GOOD",IF(SUM((--($B2:$D2<>""))*(--ISNUMBER(FIND(LEFT($B2:$D2,1),"EKD"))))>0,"OTHER","BAD"))

Alexander Frolov
  • 306
  • 2
  • 9