Need help looking up last occurrence.

[cms90740]

Hi there!

I'm calculating some basketball data and want to easily return whether a team has played the night before or not. Here is a sample of my data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]home team[/TD]
[TD]away team[/TD]
[TD]home team rest[/TD]
[TD]away team rest[/TD]
[/TR]
[TR]
[TD]4/1[/TD]
[TD]bulls[/TD]
[TD]bucks[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]4/1[/TD]
[TD]nets[/TD]
[TD]knicks[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]4/2[/TD]
[TD]warriors[/TD]
[TD]bucks[/TD]
[TD]y[/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]4/2[/TD]
[TD]nets[/TD]
[TD]celtics[/TD]
[TD]n[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]4/3[/TD]
[TD]knicks[/TD]
[TD]bulls[/TD]
[TD]y[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]4/3[/TD]
[TD]celtics[/TD]
[TD]warriors[/TD]
[TD]n[/TD]
[TD]n[/TD]
[/TR]
</tbody>[/TABLE]


So I'm trying to create a formula for the two right columns. I want to lookup when the last occurrence of column B & column C was and return a "y" if the date was more than 1 day before column A, and a "n" if the date was the day before. This is also a bit tricky being that my data is a sorted by date and not in a team order.

Any help would be greatly appreciated!

Thank you.

 

[Aryatect]

Hi,

See if this works for you:

Book1
	A	B	C	D	E
1	date	home team	away team	home team rest	away team rest
2	1-Apr	bulls	bucks	Y	Y
3	1-Apr	nets	knicks	Y	Y
4	2-Apr	bulls	bucks	N	N
5	2-Apr	nets	celtics	N	Y
6	3-Apr	knicks	bulls	Y	Y
7	3-Apr	celtics	warriors	Y	Y
Sheet9

Cell Formulas
Range	Formula
D2	{=IFERROR(IF($A2-INDEX($A$1:$A$5,SMALL(IF(B$1:B1=B2,ROW($A$1:$A1)-ROW($A$1)+1),1))>1,"Y","N"),"Y")}
E2	{=IFERROR(IF($A2-INDEX($A$1:$A$5,SMALL(IF(C$1:C1=C2,ROW($A$1:$A1)-ROW($A$1)+1),1))>1,"Y","N"),"Y")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cms90740]

Hi,

See if this works for you:

	A	B	C	D	E
date	home team	away team	home team rest	away team rest
bulls	bucks	Y	Y
nets	knicks	Y	Y
bulls	bucks	N	N
nets	celtics	N	Y
knicks	bulls	Y	Y
celtics	warriors	Y	Y

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1-Apr[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1-Apr[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2-Apr[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2-Apr[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3-Apr[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3-Apr[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IFERROR(IF($A2-INDEX($A$1:$A$5,SMALL(IF(B$1:B1=B2,ROW($A$1:$A1)-ROW($A$1)+1),1))>1,"Y","N"),"Y")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(IF($A2-INDEX($A$1:$A$5,SMALL(IF(C$1:C1=C2,ROW($A$1:$A1)-ROW($A$1)+1),1))>1,"Y","N"),"Y")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

That is insanely complex, thank you so much for taking the time to put this together. It sort of works. The only problem I can see if that E6 should actually return "N". The bulls played on April 2 - B4 and April 3 - C6. May be a complication with cross referencing the columns? Not sure if it's possible to cross reference both columns against the dates.

Thank you again for your time!

 

[Aryatect]

I think below should solve the problem:

Book1
	A	B	C	D	E
1	date	home team	away team	home team rest	away team rest
2	1-Apr	bulls	bucks	Y	Y
3	1-Apr	nets	knicks	Y	Y
4	2-Apr	bulls	bucks	N	N
5	2-Apr	nets	celtics	N	Y
6	3-Apr	knicks	bulls	Y	N
7	3-Apr	celtics	warriors	N	Y
Sheet9

Cell Formulas
Range	Formula
D2	{=IFERROR(IF($A2-INDEX($A$1:$A$5,LARGE(IF($B$1:$C1=B2,ROW($A$1:$A1)-ROW($A$1)+1),1))>1,"Y","N"),"Y")}
E2	{=IFERROR(IF($A2-INDEX($A$1:$A$5,LARGE(IF($B$1:$C1=C2,ROW($A$1:$A1)-ROW($A$1)+1),1))>1,"Y","N"),"Y")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

I annex 2 options with regular formula.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:99.8px;" /><col style="width:89.35px;" /><col style="width:115.96px;" /><col style="width:124.51px;" /><col style="width:142.57px;" /><col style="width:116.91px;" /><col style="width:128.32px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">date</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">home team</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">away team</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">home team rest</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">away team rest</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">home team rest</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">away team rest</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01/04/2019</td><td >bulls</td><td >bucks</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01/04/2019</td><td >nets</td><td >knicks</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">02/04/2019</td><td >warriors</td><td >bucks</td><td style="text-align:center; ">y</td><td style="text-align:center; ">n</td><td style="text-align:center; ">y</td><td style="text-align:center; ">n</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">02/04/2019</td><td >nets</td><td >celtics</td><td style="text-align:center; ">n</td><td style="text-align:center; ">y</td><td style="text-align:center; ">n</td><td style="text-align:center; ">y</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">03/04/2019</td><td >knicks</td><td >bulls</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td><td style="text-align:center; ">y</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">03/04/2019</td><td >celtics</td><td >warriors</td><td style="text-align:center; ">n</td><td style="text-align:center; ">n</td><td style="text-align:center; ">n</td><td style="text-align:center; ">n</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IF(COUNTIFS($B$2:$B2,B2,$A$2:$A2,$A2-1)+COUNTIFS($C$2:$C2,B2,$A$2:$A2,$A2-1)=0,"y","n")</td></tr><tr><td >F2</td><td >=IF(SUMPRODUCT(($A$2:$A2=$A2-1)*(($B$2:$B2=B2)+($C$2:$C2=B2)))=0,"y","n")</td></tr></table></td></tr></table>

Formula in D2 copy to E2 and then copy down
Formula in F2 copy to G2 and then copy down