Nested If problem to tag line items with "Keep" or "Remove" - not able to find the right combination

[DougJefferson99]

To my more experienced excel users,

I have a worksheet that has 50 K lines plus and I have simplified my issue below

Col A Col B Col C
Aging In Prior Report Keep/Remove

61-90 days Y
61-90 days Y
0-30 days Y
31-60 days Y
0-30 days N
91-120 days Y

I need to build a nested if statement that looks at col B and if it is a "Y" then look at col A and if it is "0-30 days" or "31-60 days" have Col C return "Remove" if it is any other aging range return "Keep" also if Col B is "N" then I need Col C return "Keep"

I have tried many attempts and this is my last one but it still will not work.

Any assistance would be greatly appreciated.

Thank you in advance.

 

[jasonb75]

This should do it,

=IF(OR(B2="N",AND(B2="Y",OR(A2="0-30 days",A2="31-60 days"))),"Keep","Remove")

 

[jtakw]

Hi,

Wrote the formula 2 different ways for you:

Book3.xlsx
	A	B	C	D
1	61-90 days	Y	Keep	Keep
2	61-90 days	Y	Keep	Keep
3	0-30 days	Y	Remove	Remove
4	31-60 days	Y	Remove	Remove
5	0-30 days	N	Keep	Keep
6	91-120 days	Y	Keep	Keep
Sheet852

Cell Formulas
Range	Formula
C1:C6	C1	=IF(B1="Y",IF(OR(A1={"0-30 days","31-60 days"}),"Remove","Keep"),"Keep")
D1:D6	D1	=IF(AND(B1="Y",OR(A1={"0-30 days","31-60 days"})),"Remove","Keep")

 

[DougJefferson99]

Jasonb75 thank you, I tried this and I thought it worked but then saw the "Keep" and "Remove" looked backwards in the formula since I saw results of 181+ days and Y returned "N". I I fliped these around and it seems to be correct except now I see tried it and it's almost got it. I see an issue when Col B = "N" and Col A = "181+days" this should be a "Keep" in Col C.. It seems that any combination of aging day's greater then 61-90 days and N return Remove and should be keep.

 

[DougJefferson99]

Jason, I just saw your 2nd post with the two formulas and Row 5 needs to return "remove" along with any that would say 31-60 days and N.

I really appreciate your help.

 

[jtakw]

That's my post, not Jason's.

So it doesn't matter whether Column B is "Y" or "N", which contradicts your OP description:

Book3.xlsx
	A	B	C
1	61-90 days	Y	Keep
2	61-90 days	Y	Keep
3	0-30 days	Y	Remove
4	31-60 days	Y	Remove
5	0-30 days	N	Remove
6	91-120 days	Y	Keep
7	0-30 days	N	Remove
8	31-60 days	N	Remove
Sheet852

Cell Formulas
Range	Formula
C1:C8	C1	=IF(OR(A1={"0-30 days","31-60 days"}),"Remove","Keep")

 

[DougJefferson99]

That did the trick thank you so much. I appreciate it.

 

[jtakw]

You're welcome, thanks for the feedback.