highest date with condition

Tschiku

New Member
Joined
Oct 14, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello all,

(i'm learning alot with this forum!!! Thank you all!!!)

I need your help, because i'm banging with my head on the wall...

I Have a table, with accounts, and ID that were created on a data. The same account may have different activations, wich one with a different id, with a new creation date:

TABLE 1
ABCD
ACCOUNTIDCreation DateAmount Id's
70000047000004002630/05/20141
70000055891208/04/20202
70000052811902/08/20172
70000077000007009630/05/20141
70000087000008008701/02/20191
70000607000060000728/08/20191
70000647000064006802/01/20201
70000661462211/02/20162
70000667000066005030/05/20202
70000677000067004130/05/20141
7000070204813/08/20143
70000707000070001430/05/20143
70000707000073008401/04/20203
70000807000080002130/05/20141


Afer a new contact, i need to get the nearest high date after contact (new activation), after the date contact, with the id associated to it (the last two columns, are the expected date and expecte result id).
TABLE 2
Contact - ACCOUNTDate_ContactExpected Result DateExpected Result ID
700000412/02/2020blankblank
700000501/12/201908/04/202058912
700000513/03/202008/04/202058912
700000701/08/2019blankblank
700000815/01/201901/02/201970000080087
700006001/07/201928/08/201970000600007
700006614/02/2019blankblank
700006601/08/2020blankblank
700006701/12/201330/05/201470000670041
700007028/04/201430/05/201470000700014
700007001/01/202001/04/202070000730084
700007001/01/201230/05/201470000700014
700007001/07/201413/08/20142048

I appreciate your help! Thank you

TS





 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to the MrExcel forum!

Given that you have Excel 365, you should have the new array formulas. So given this layout:

Dynamic functions.xlsm
ABCDEFGHI
1ACCOUNTIDCreation DateAmount Id'sContact - ACCOUNTDate_ContactExpected Result DateExpected Result ID
27000004700000400265/30/2014170000042/12/2020
37000005589124/8/20202700000512/1/20194/8/202058912
47000005281198/2/2017270000053/13/20204/8/202058912
57000007700000700965/30/2014170000078/1/2019
67000008700000800872/1/2019170000081/15/20192/1/201970000080087
77000060700006000078/28/2019170000607/1/20198/28/201970000600007
87000064700006400681/2/2020170000662/14/20195/30/202070000660050
97000066146222/11/2016270000668/1/2020
107000066700006600505/30/20202700006712/1/20135/30/201470000670041
117000067700006700415/30/2014170000704/28/20145/30/201470000700014
12700007020488/13/2014370000701/1/20204/1/202070000730084
137000070700007000145/30/2014370000701/1/20125/30/201470000700014
147000070700007300844/1/2020370000707/1/20148/13/20142048
157000080700008000215/30/20141
Sheet7


Put this formula in H2 and copy down:

Excel Formula:
=IFERROR(INDEX(SORT(FILTER($A$2:$C$15,($A$2:$A$15=F2)*($C$2:$C$15>=G2)),3),1,3),"")

and this in I2 and copy down:

Excel Formula:
=IFERROR(INDEX(SORT(FILTER($A$2:$C$15,($A$2:$A$15=F2)*($C$2:$C$15>=G2)),3),1,2),"")


If you don't have SORT or FILTER, the H column would be easy enough to do with MAXIFS or an array formula, but the I column would be trickier. Full disclosure: I don't have the new functions yet, but I tested the formula using UDFs.
 
Upvote 0
The underrated situation when some people don´t help others, is because they don't understand how supportive is to help someone to learn with the way of thinking on the formulas!

This helped me alot!!! Many kudos to you ERIC W!!!
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

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