Formula to identify if a common account number has a corresponding date within range of another date

E

eagerexceler

New Member
Joined
Jan 10, 2017
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
Hello y'all,

I have a convoluted spreadsheet that has many rows of data that includes a common identifier in column A, a unique identifier in column D, and a unique date in column F. All the way over in column AY and AZ I need to conditionally populate the cell if the common identifier in column A is present on another row if the date in column F occurs within a set timeframe. I hope the attached data sheet helps clarify.

Basically, if cell A5 has a date within the range present in cell AY3 in any other row I need to populate cell AY5 with "Oth Present". Otherwise "Oth Not Present".

Thank you in advance!

Excel Nest If Date Within Another Date Example.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBA
1
2                         
312345678910111213141516171819202122232425303303
4##-1Provider #2#BegEndIndCalcNameTot1Tot2ActAmtCatSLReCHExCY19MarProAd 1Ad 2Ad 3Ad 4Ad 5Ad 6Ad 7Ad 8Ad 9Ad 10Ad 11Ad 12Ad 13Ad 14Ad 15Ad 16Ad 17Ad 18Ad 19Ad 20Ad 21Ad 22Ad 23Ad 24Ad 25W/In 30?W/In 3?Ad ExOth W/In 30?Oth W/In 3?Oth Exp
511-11111/1/191/4/19I32xxxxxxxxxxxxxxxxxxxxx(blank)0018089.41ba1/1/2019                        
622-11221/1/191/4/19I391xxxxxxxxxxxxxxxxxxxxx(blank)0011151.21ba1/1/2019                        
733-11331/3/192/15/19I1xxxxxxxxxxxxxxxxxxxxx(blank)003511191ba1/3/2019                        
844-11441/4/191/11/19I558xxxxxxxxxxxxxxxxxxxxx(blank)0061251ba1/4/2019                        
955-11751/11/191/25/19I668xxxxxxxxxxxxxxxxxxxxx(blank)0013378.41ba11/1/201912/1/201912/20/2019                      
1055-21555/31/196/7/19I653xxxxxxxxxxxxxxxxxxxxx(blank)0037537.61ba11/1/20195/31/20196/28/2019                      
1155-316512/20/1912/23/19I280xxxxxxxxxxxxxxxxxxxxx(blank)0011252.51ba11/1/20195/31/201912/20/2019                      
12
13
Sheet2
Cell Formulas
RangeFormula
Z5:AU11,W5:Y8,W2:AU2W2=IFNA(IF($H2="I",VLOOKUP($A2&"-"&W$3,$B:$F,5,FALSE),""),"")
X3:AU3X3=W3+1
A5:A11A5=E5
B5:B11B5=IF($H5="I",$E5&"-"&COUNTIFS($E$5:$E5,$E5,$H$5:$H5,"I"),"OP")
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
The formula I'm trying to work with is =IF(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>VLOOKUP($A5,$A:$F,6,FALSE),"Oth Present","Oth Not Present"), but I don't think it's looking at all instances of the dates in column F.
 
Upvote 0
eagerexceler said:
The formula I'm trying to work with is =IF(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>VLOOKUP($A5,$A:$F,6,FALSE),"Oth Present","Oth Not Present"), but I don't think it's looking at all instances of the dates in column F.
Click to expand...
Did you mean to bind the first VLOOKUP to only look at the same row? notice $A5:$F5 is only the same row as the formula in AY5.... so for the examples you provided above, the IF statement is always IF(X+30 > X, "Oth Present").... it's always returning Oth Present because everything is just comparing a number+30 against that same number, so it's always TRUE.
 
Upvote 0
Hi, see if it works, assuming within 30 days means equal or less than 30 days before or after.
Book1
ABFHAVAWAXAYAZBA
1
2
3303303
4##-1BegIndW/In 30?W/In 3?Ad ExOth W/In 30?Oth W/In 3?Oth Exp
511-11/1/2019IOth Not Present
622-11/1/2019IOth Not Present
733-11/3/2019IOth Not Present
844-11/4/2019IOth Not Present
955-11/11/2019IOth Present
1055-25/31/2019IOth Not Present
1155-312/20/2019IOth Not Present
1255-42/10/2019IOth Present
1355-512/12/2018IOth Present
Sheet1
Cell Formulas
RangeFormula
A5:A13A5=E5
B5:B13B5=IF($H5="I",$E5&"-"&COUNTIFS($E$5:$E5,$E5,$H$5:$H5,"I"),"OP")
F12F12=F9+30
F13F13=F9-30
AY5:AY13AY5=IF(ISERR(AGGREGATE(15,6,(ROW($A$5:$A$13)/($A$5:$A$13=$A5)/(ABS($F$5:$F$13-$F5)<=AY$3)),2)>0),"Oth Not Present","Oth Present")
 
Upvote 0
Just noticed what you said, "assuming within 30 days means equal or less than 30 days before or after." This is only looking for greater than 30d, not less than:

=IF(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>VLOOKUP($A5,$A:$F,6,FALSE),"Oth Present","Oth Not Present")

would need to include the boundary condition in the other direction as an OR (this probably won't be right for your situaiton, but just to show you how an OR inside the IF would look):

=IF(OR(VLOOKUP($A5,$A5:$F5,6,FALSE)+AY$3>=VLOOKUP($A5,$A:$F,6,FALSE), VLOOKUP($A5,$A5:$F5,6,FALSE)-AY$3>=VLOOKUP($A5,$A:$F,6,FALSE)), "Oth Present","Oth Not Present")
 
Last edited:
Upvote 0
Hey y'all, thank you for your help here.

Habtest - this formula mostly works, though I have to switch the "Oth Present" and "Oth Not Present". Also, I only want the formula to look at 30 days after, not before. Can't figure out how to alter this accordingly.

Thanks!
 
Upvote 0
eagerexceler said:
Hey y'all, thank you for your help here.

Habtest - this formula mostly works, though I have to switch the "Oth Present" and "Oth Not Present". Also, I only want the formula to look at 30 days after, not before. Can't figure out how to alter this accordingly.

Thanks!
Click to expand...
Hey, please see the revision, it now looks for 1 to 30 days after (both boundaries included).

Book1
ABFGHAVAWAXAYAZ
3303303
4##-1BegIndW/In 30?W/In 3?Ad ExOth W/In 30?Oth W/In 3?
511-11/1/2019IOth Not Present
622-11/1/2019IOth Not Present
733-11/3/2019IOth Not Present
844-11/4/2019IOth Not Present
955-11/11/2019IOth Present
1055-25/31/2019IOth Not Present
1155-312/20/2019IOth Not Present
1255-42/10/2019IOth Not Present
1355-512/12/2018IOth Present
Sheet1
Cell Formulas
RangeFormula
F12F12=F9+30
F13F13=F9-30
AY5:AY13AY5=IF(ISERR(AGGREGATE(15,6,(ROW($A$5:$A$13)/($A$5:$A$13=$A5)/($F$5:$F$13-$F5<=AY$3)/($F$5:$F$13>$F5)),1)>0),"Oth Not Present","Oth Present")
 
Upvote 0
Hey Habtest - for some reason I'm still getting some results where the first date of an account number in column A is returning "Oth Present". Do you know why this is?

Appreciate your help!!
 
Upvote 0
For the results in #7 column AY, is there any that is not what you expect? Please note row 12 and 13 are added for illustration purpose and they are not in the data in your original post #1, and they change the result in other row (row 5).

Or if the issue is not with the mock data above, please upload relevant sample. We'd need those to understand what went wrong.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top