Index, Match and Rank

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
530
Hello
What I am attempting to do is have Index and Match pull the time and class and sort by earliest time for that day. I thought I could rank the times first to show the earliest time and then index and match to that column. It worked for the first date of 7/1. When I changed the date to 7/2, the first entry is showing the information from 7/1. It should show a time of 8:10 and Class E1.

Second question, if it does work, I was wondering if I can combine the two formulas in column I.
Any help is appreciated.
Book1
ABCDEFGHIJ
1DateTime Class7/1/2020RankTime ClassCombined
27/1/20202:00 PME3310:00 AME810:00 AM
37/1/20201:30 PME521:30 PME51:30 PM
47/2/20208:10 AME102:00 PME32:00 PM
57/1/202010:00 AME81#N/A#N/A
67/2/20204:00 PMb13#N/A#N/A
77/3/202012:00 PMb21#N/A#N/A
87/5/202011:30 AMb31#N/A#N/A
9
10DateTime Class7/2/2020RankTime ClassCombined
117/1/20202:00 PME312:00 PME32:00 PM
127/1/20201:30 PME514:00 PMb14:00 PM
137/2/20208:10 AME11#N/A#N/A#N/A
147/1/202010:00 AME81#N/A#N/A
157/2/20204:00 PMb12#N/A#N/A
167/3/202012:00 PMb21#N/A#N/A
Sheet2
Cell Formulas
RangeFormula
F2:F4, F5:F8F2=COUNTIFS($B$2:$B$8,"<="&B2,$A$2:$A$8,"="&$E$1)
G2:G4, G5:G8G2=INDEX($B$2:$B$8,MATCH(ROWS(G$2:$G2),$F$2:$F$8,0))
H2:H4, H14:H16, H11:H13, H5:H8H2=INDEX($C$2:$C$8,MATCH(G2,$B$2:$B$8,0),MATCH($E$1,$A$2:$A$8,0))
I2:I4I2=COUNTIFS($B$2:$B$8,"<="&B2,$A$2:$A$8,"="&$E$1)+INDEX($B$2:$B$8,MATCH(ROWS(G$2:$G2),$F$2:$F$8,0))
F11:F13, F14:F16F11=COUNTIFS($B$11:$B$17,"<="&B11,$A$11:$A$17,"="&$E$10)
G11:G13, G14:G16G11=INDEX($B$11:$B$17,MATCH(ROWS(G$11:$G11),$F$11:$F$17,0))
I11:I13I11=COUNTIFS($B$11:$B$17,"<="&B11,$A$11:$A$17,"="&$E$10)+INDEX($B$11:$B$17,MATCH(ROWS(G$11:$G11),$F$11:$F$17,0))
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,151
Office Version
  1. 2016
Platform
  1. Windows
Hi Rex759,

Let me come at this a different way. Does this do what you want?

Rex759.xlsx
ABCDEFG
1DateTime Class7/1/2020TimeClass
27/1/20202:00 PME310:00 AME8
37/1/20201:30 PME51:30 PME5
47/2/20208:10 AME11:30 PMb2
57/1/202010:00 AME82:00 PME3
67/2/20204:00 PMb1  
77/1/20201:30 PMb2  
87/5/202011:30 AMb3  
9
10DateTime Class7/2/2020TimeClass
117/1/20202:00 PME38:10 AME1
127/1/20201:30 PME54:00 PMb1
137/2/20208:10 AME1  
147/1/202010:00 AME8  
157/2/20204:00 PMb1  
167/3/202012:00 PMb2  
Sheet1 (2)
Cell Formulas
RangeFormula
F2:F8F2=IF(COUNTIF($A$2:$A$8,$E$1)>=(ROW()-ROW($F$1)),AGGREGATE(15,6,($B$2:$B$8)/($A$2:$A$8=$E$1),ROW()-ROW($F$1)),"")
G2:G8G2=IF(F2="","",INDEX($C$2:$C$8,AGGREGATE(15,6,ROW($A$2:$A$8)-ROW($A$1)/(($A$2:$A$8=$E$1)*($B$2:$B$8=F2)),COUNTIF($F$2:$F2,F2))))
F11:F16F11=IF(COUNTIF($A$11:$A$16,$E$10)>=(ROW()-ROW($F$10)),AGGREGATE(15,6,($B$11:$B$16)/($A$11:$A$16=$E$10),ROW()-ROW($F$10)),"")
G11:G16G11=IF(F11="","",INDEX($C$11:$C$16,AGGREGATE(15,6,ROW($A$11:$A$16)-ROW($A$10)/(($A$11:$A$16=$E$10)*($B$11:$B$16=F11)),COUNTIF($F$11:$F11,F11))))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,273
Messages
5,571,253
Members
412,374
Latest member
Nagelgal
Top