Originally Posted by
JeteMc
Here is a fairly lengthy proposal for the tricky requirements.
1. Notice that on the File-2 sheet the range is changed to a table with two extra columns.
A. The three original columns are custom sorted first by Key and then by Departure.
B. The Connections column is populated using: =COUNTIFS('File-1'!A$2:A$20,A2,'File-1'!B$2:B$20,"<="&B2)
C. The Order column is populated using: =IF(OR(D2=0,COUNTIFS(A$2:A2,A2,D$2:D2,D2)>D2),0,COUNTIFS(A$2:A2,A2,D$2:D2,D2))
2. A helper range, which may be moved and/or hidden for aesthetic purposes, is placed in H:M
A. Column H is populated using: =IFERROR(INDEX(A$2:A$16,AGGREGATE(15,6,(ROW(A$2:A$16)-ROW(A$1))/(E$2:E$16>0),ROWS(A$1:A1))),"")
B. Column I is populated using: =IF(H2="","",AGGREGATE(15,6,('File-1'!$B$2:$B$13)/('File-1'!A$2:A$13=H2),COUNTIFS(H$2:H2,H2)))
C. Columns J:K are populated using: =IF(H2="","",INDEX(B$2:B$16,AGGREGATE(15,6,(ROW(A$2:A$16)-ROW(A$1))/($E$2:$E$16>0),ROWS(A$1:A1))))
D. Column L is populated using: =IF(H2="","",VALUE(MID(K2,3,4)))
E. Column M is populated using: =IF(H2="","",RANK.EQ(L2,L$2:L$16,1))
3. The output, highlighted in green, on the OP sheet is populated using: =IFERROR(INDEX('File-2'!H$2:H$16,MATCH(ROWS(A$1:A1),'File-2'!$M$2:$M$16,0)),"")
Perhaps one of the other contributors will be able to shorten the process.
Let us know if you have any questions.
Bookmarks