multiple if conditions to tag relevant records

[nikhil0311]

Excel 2007
	A	B	C	D	E	F	G	H
1	Include/Exclude	Parent CASID	Date	Region	Product Mapping	Stage	Stage 2	Relevant (Y/N)
2	Include	Private	2015	APAC	CC	Bid	Lost
3		-	2016	CAN	Liq	Mandate	Dead
4		TBD	2017	EMEA	FX	Opp	Yes
5			2018	LATAM	Lend	Lost	No
6			2020	Others	Paymentech	NQO	Probably
7			2025	US	Trade	Closed
8			2065		Other
9					IB
10					Multiple Products
Sheet1

[TABLE="width: 83"]
<colgroup><col></colgroup><tbody>[TR]
[TD]If Column A. Include/Exclude = Include[/TD]
[/TR]
[TR]
[TD]AND

[/TD]
[/TR]
[TR]
[TD]Column B. Parent CASID <> "Private","-","TBD"
[/TD]
[/TR]
[TR]
[TD]
AND

[/TD]
[/TR]
[TR]
[TD]Column C. date >=2017
[/TD]
[/TR]
[TR]
[TD]
AND

[/TD]
[/TR]
[TR]
[TD]Column D. Region = "APAC,"CAN","EMEA","LATAM","US","Others"
[/TD]
[/TR]
[TR]
[TD]
AND
[/TD]
[/TR]
[TR]
[TD]
Column E. Product Mapping have 3 different conditions

[/TD]
[/TR]
[TR]
[TD]1 - if "Paymentech" then exclude
[/TD]
[/TR]
[TR]
[TD]2 - if Column D. i.e. Region is "US" or "Others" AND Column E.i.e. Product Mapping = "Trade" then Include as relevant
[/TD]
[/TR]
[TR]
[TD]3 - all remaining regions i.e."APAC", "CAN", "EMEA", "LATAM" with any products except "Paymentech" should be considered as relevant
[/TD]
[/TR]
[TR]
[TD]
AND

[/TD]
[/TR]
[TR]
[TD]Column F. Stage = "Bid", "Closed","Lost","Mandate","NQO","Opp"
[/TD]
[/TR]
[TR]
[TD]
AND

[/TD]
[/TR]
[TR]
[TD]If Column F i.e. Stage = "Lost" AND Column G i.e. Stage 2 = "Lost" then consider consider as relevant record
[/TD]
[/TR]
[TR]
[TD]all of the above conditions should be tagged as "Y" in Column H i.e. Relevant Y/N
[/TD]
[/TR]
</tbody>[/TABLE]

 

[inactiveuserps07]

@nikhil0311, I followed up until this:

_____________________________________

[TABLE="class: cms_table, width: 83"]
<tbody>[TR]
[TD]Column F. Stage = "Bid", "Closed","Lost","Mandate","NQO","Opp"[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]If Column F i.e. Stage = "Lost" AND Column G i.e. Stage 2 = "Lost" then consider consider as relevant record

_____________________________________[/TD]
[/TR]
</tbody>[/TABLE]

Are you saying count as relevant if all prior conditions have been met and Column F contains any of the blue words, regardless of what's in Column G, except only count Column F = "Lost" if Column G also = "Lost"?

What if Column F="Lost" and Column G is blank? Still relevant? Or not?

What if Column F="Lost" and Column G = "yes"? Not relevant?

Will Column G ever be filled if it's corresponding cell in Column F doesn't have "Fill"?

 

[inactiveuserps07]

@nikhil0311, It's not clear whether you're looking for a VBA solution or a formula-based solution. If you are looking for a formula-based solution, and given the example in your original post, enter the following formula into cell H2 and drag-copy down as far as needed:

=IF(AND(TRIM(UPPER(A2))="INCLUDE",OR(TRIM(B2)="",ISERROR(SEARCH(TRIM(B2),"Private-TBD"))),C2>=2017,AND(TRIM(D2)<>"",ISNUMBER(SEARCH(TRIM(D2),"APAC-CAN-EMEA-LATAM-US-Others"))),TRIM(E2)<>"PAYMENTECH",IF(ISNUMBER(SEARCH(TRIM(D2),"US-Others")),IF(UPPER(E2)="TRADE",TRUE,FALSE),TRUE),AND(TRIM(F2)<>"",ISNUMBER(SEARCH(TRIM(F2),"Bid-Closed-Lost-Mandate-NQO-Opp"))),IF(TRIM(UPPER(F2))="LOST",IF(TRIM(UPPER(G2))="LOST",TRUE,FALSE),TRUE)),"Y","N")

I'm fairly sure I covered all the bases, at least as I understood them.

 

[nikhil0311]

Are you saying count as relevant if all prior conditions have been met and Column F contains any of the blue words, regardless of what's in Column G, except only count Column F = "Lost" if Column G also = "Lost"? - YES

What if Column F="Lost" and Column G is blank? Still relevant? Or not? - - Not Relevant

What if Column F="Lost" and Column G = "yes"? Not relevant? - Not Relevant

Will Column G ever be filled if it's corresponding cell in Column F doesn't have "Fill"? - Column G is only considered to fulfill if F = Lost and G is also lost.

 

[nikhil0311]

Hi Erik, I need a Formula. Also would it be possible to use values from specific range instead of hard-coding?

 

[inactiveuserps07]

@nikhil0311, I'm not sure I understand your question above. The general answer is, yes, you can change the cell references in my formula to reflect the actual cell references in your own sheet. Beyond that, you'll have to be more specific.

 

[nikhil0311]

for instance, column B have "-", "TBD" and "private" values. their might be a possibility in the near future it will have new values. In that case I will have to manually update the formula every time whenever there is a new value. so is it possible to automate this?

 

[nikhil0311]

Hi Erik, Thanks a lot for your help. Your Formula is working perfectly fine. I have a small change. In Column F i.e. Stage I want to exclude "-" records. can you please let me know how to achieve this?

 

[inactiveuserps07]

@nikhil0311, here is the most flexible modification, per your above specification:

=IF(AND(TRIM(UPPER(A2))="INCLUDE",OR(TRIM(B2)="",ISERROR(SEARCH(TRIM(B2),"Private-TBD"))),C2>=2017,AND(TRIM(D2)<>"",ISNUMBER(SEARCH(TRIM(D2),"APAC-CAN-EMEA-LATAM-US-Others"))),TRIM(E2)<>"PAYMENTECH",IF(ISNUMBER(SEARCH(TRIM(D2),"US-Others")),IF(UPPER(E2)="TRADE",TRUE,FALSE),TRUE),AND(TRIM(F2)<>"",ISNUMBER(SEARCH(TRIM(F2),"Bid-Closed-Lost-Mandate-NQO-Opp"))),TRIM(F2)<>"-",IF(TRIM(UPPER(F2))="LOST",IF(TRIM(UPPER(G2))="LOST",TRUE,FALSE),TRUE)),"Y","N")

I sent you a PM as well.