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





 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
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.
 

Tschiku

New Member
Joined
Oct 14, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,138
Messages
5,546,155
Members
410,731
Latest member
keobongmacao
Top