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

DougJefferson99

New Member
Joined
Mar 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,676
Office Version
  1. 365
Platform
  1. Windows
This should do it,

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Wrote the formula 2 different ways for you:

Book3.xlsx
ABCD
161-90 daysYKeepKeep
261-90 daysYKeepKeep
30-30 daysYRemoveRemove
431-60 daysYRemoveRemove
50-30 daysNKeepKeep
691-120 daysYKeepKeep
Sheet852
Cell Formulas
RangeFormula
C1:C6C1=IF(B1="Y",IF(OR(A1={"0-30 days","31-60 days"}),"Remove","Keep"),"Keep")
D1:D6D1=IF(AND(B1="Y",OR(A1={"0-30 days","31-60 days"})),"Remove","Keep")
 

DougJefferson99

New Member
Joined
Mar 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
ABC
161-90 daysYKeep
261-90 daysYKeep
30-30 daysYRemove
431-60 daysYRemove
50-30 daysNRemove
691-120 daysYKeep
70-30 daysNRemove
831-60 daysNRemove
Sheet852
Cell Formulas
RangeFormula
C1:C8C1=IF(OR(A1={"0-30 days","31-60 days"}),"Remove","Keep")
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,167
Messages
5,640,540
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top