Find names based on date

[Sparda142]

Hello,


I'm looking for 2 formulas or a macros that do the following. The first formula; In column D1 will give a date based on the data corresponding in the cell range if the name corresponds in column B and is within columns A1:A6. I'm not sure how to say find the date in column A and grab the data from corresponding cells in Column B however only up to the next date in column A.

The second formula is need is to place names in column E based on the date in column D

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/4/2019[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]jacob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]john[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8/5/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]john[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]jacob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]jill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]8/6/2019[/TD]
[TD]bill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]8/7/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]john[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

You need something like this:

<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:91.25px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:101.7px;" /><col style="width:76.04px;" /></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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">08/04/2019</td><td >John</td><td > </td><td style="text-align:right; ">08/04/2019</td><td >John</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td >jacob</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >jacob</td><td > </td><td > </td><td >Jill</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td >john</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >Jill</td><td > </td><td style="text-align:right; ">08/05/2019</td><td >john</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >john</td><td > </td><td > </td><td >jacob</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">08/05/2019</td><td > </td><td > </td><td > </td><td >jill</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >john</td><td > </td><td style="text-align:right; ">08/06/2019</td><td >bill</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >jacob</td><td > </td><td > </td><td >joe</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td style="text-align:right; ">08/07/2019</td><td >john</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >jill</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">08/06/2019</td><td >bill</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td >joe</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">08/07/2019</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td >john</td><td > </td><td > </td><td > </td></tr></table>



Are there really blank cells between dates and between names?

 

[Sparda142]

You need something like this:

	A	B	C	D	E
John		John
				jacob
	jacob			Jill
				john
	Jill		john
	john			jacob
			jill
	john		bill
	jacob			joe
			john
	jill
bill
	joe
	john

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91.25px;"><col style="width:76.04px;"><col style="width:76.04px;"><col style="width:101.7px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]08/04/2019[/TD]

[TD="align: right"]08/04/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]08/05/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]08/05/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]08/06/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]08/07/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]08/06/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]08/07/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

</tbody>

Are there really blank cells between dates and between names?

In column B there are blanks however in Column D, D2 should have a date.

 

[DanteAmor]

You can put the desired result

 

[Peter_SSs]

Still not very clear to me, but see if this is it.
If not, as Dante has requested, please post the table from post 1 again but also include in the table the exact results you want in columns D & E.

Try copying these formulas down as far as you might need.

Excel Workbook
	A	B	C	D	E
1	Date	Name		Date	Name
2	8/04/2019	John		8/04/2019	John
3				8/04/2019	jacob
4		jacob		8/04/2019	Jill
5				8/04/2019	john
6		Jill		8/05/2019	john
7		john		8/05/2019	jacob
8	8/05/2019			8/05/2019	jill
9		john		8/06/2019	bill
10		jacob		8/06/2019	joe
11				8/07/2019	john
12		jill
13
14	8/06/2019	bill
15		joe
16
17	8/07/2019
18		john
19
20
Find Names Based On Date

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

 

[Sparda142]

	A	B	C	D	E
Name		Name
John		John
			jacob
	jacob		Jill
			john
	Jill		john
	john		jacob
		jill
	john		bill
	jacob		joe
			john
	jill
bill
	joe
	john

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Date[/TD]

[TD="align: center"]Date[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8/04/2019[/TD]

[TD="align: center"]8/04/2019[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]8/04/2019[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]8/04/2019[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]8/04/2019[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]8/05/2019[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8/05/2019[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8/05/2019[/TD]

[TD="align: center"]8/05/2019[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]8/06/2019[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]8/06/2019[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]8/07/2019[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]8/06/2019[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]8/07/2019[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

</tbody>

The formula to generates names works however the formula to generate dates didn't work, or maybe i didn't correctly change it to my needs. The way Column D and E was posted was correct. I have changed the indicated columns however it didnt work.

=IF(N10="","",LOOKUP(9.99E+307,A$10:INDEX(A$10:A$31008,AGGREGATE(15,6,(ROW(A$10:A$31008)-ROW($A$10)+1)/(B$10:B$31008=N10),COUNTIF(N$10:N10,N10)))))

 

[DanteAmor]

Try this array formulas:

<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:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:78.89px;" /><col style="width:76.04px;" /></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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Date</td><td >Name</td><td > </td><td >Date</td><td >Name</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">08/04/2019</td><td >John</td><td > </td><td style="text-align:right; ">08/04/2019</td><td >John</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td style="text-align:right; ">08/04/2019</td><td >Jacob</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >Jacob</td><td > </td><td style="text-align:right; ">08/04/2019</td><td >Jill</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td style="text-align:right; ">08/04/2019</td><td >john</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >Jill</td><td > </td><td style="text-align:right; ">08/05/2019</td><td >john</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >john</td><td > </td><td style="text-align:right; ">08/05/2019</td><td >jacob</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">08/05/2019</td><td > </td><td > </td><td style="text-align:right; ">08/05/2019</td><td >jill</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >john</td><td > </td><td style="text-align:right; ">08/06/2019</td><td >bill</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >jacob</td><td > </td><td style="text-align:right; ">08/06/2019</td><td >joe</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td style="text-align:right; ">08/07/2019</td><td >john</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >jill</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">08/06/2019</td><td >bill</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td >joe</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">08/07/2019</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td >john</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td><td > </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 >Array Formula</td></tr><tr><td >D2</td><td >{=INDEX($A$1:$A$99,LARGE(IF($A$2:$A$99<>"",IF(ROW($A$2:$A$99)<=SMALL(IF($B$2:$B$99=E2,ROW($B$2:$B$99)),COUNTIF($E$2:E2,E2)),ROW($A$2:$A$99))),1))}</td></tr><tr><td >E2</td><td >{=IFERROR(INDEX($B$1:$B$99,SMALL(IF($B$2:$B$99<>"",ROW($B$2:$B$99)),ROWS($G$2:G2))),"")}</td></tr></table></td></tr></table>


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Sam_D_Ben]

Solutions like bullet shots

 

[Peter_SSs]

The formula to generates names works however the formula to generate dates didn't work, or maybe i didn't correctly change it to my needs. The way Column D and E was posted was correct. I have changed the indicated columns however it didnt work.

=IF(N10="","",LOOKUP(9.99E+307,A$10:INDEX(A$10:A$31008,AGGREGATE(15,6,(ROW(A$10:A$31008)-ROW($A$10)+1)/(B$10:B$31008=N10),COUNTIF(N$10:N10,N10)))))

You appear to have adapted the formula correctly.
Here it is working for me.

When a forum suggestion does not work for you, it doesn't help just saying it "didn't work". That gives us nothing to go on to try to resolve the problem. ;0
You need to say in what way it didn't work, For example
- It gave an error message (what message)
- It returned the incorrect result.(what result did it return for what sample data and what result should it have returned
- It crashed Excel
etc

Excel Workbook
	A	B	M	N
9	Date	Name	Date	Name
10	8/04/2019	John	8/04/2019	John
11			8/04/2019	jacob
12		jacob	8/04/2019	Jill
13			8/04/2019	john
14		Jill	8/05/2019	john
15		john	8/05/2019	jacob
16	8/05/2019		8/05/2019	jill
17		john	8/06/2019	bill
18		jacob	8/06/2019	joe
19			8/07/2019	john
20		jill
21
22	8/06/2019	bill
23		joe
24
25	8/07/2019
26		john
27
Find Names Based On Date