Find names based on date

Sparda142

Board Regular
Joined
Dec 19, 2018
Messages
52
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

abcdefgh
18/4/2019John
2
3jacob
4
5Jill
6john
78/5/2019
8john
9jacob
10
11jill
12
138/6/2019bill
14joe
15
168/7/2019
17john

<tbody>
</tbody>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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?
 
Upvote 0
You need something like this:

ABCDE
108/04/2019John 08/04/2019John
2 jacob
3 jacob Jill
4 john
5 Jill 08/05/2019john
6 john jacob
708/05/2019 jill
8 john 08/06/2019bill
9 jacob joe
10 08/07/2019john
11 jill
12
1308/06/2019bill
14 joe
15
1608/07/2019
17 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>
</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.
 
Upvote 0
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
ABCDE
1DateNameDateName
28/04/2019John8/04/2019John
38/04/2019jacob
4jacob8/04/2019Jill
58/04/2019john
6Jill8/05/2019john
7john8/05/2019jacob
88/05/20198/05/2019jill
9john8/06/2019bill
10jacob8/06/2019joe
118/07/2019john
12jill
13
148/06/2019bill
15joe
16
178/07/2019
18john
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.
 
Upvote 0
ABCDE
1DateNameDateName
28/04/2019John8/04/2019John
38/04/2019jacob
4jacob8/04/2019Jill
58/04/2019john
6Jill8/05/2019john
7john8/05/2019jacob
88/05/20198/05/2019jill
9john8/06/2019bill
10jacob8/06/2019joe
118/07/2019john
12jill
13
148/06/2019bill
15joe
16
178/07/2019
18john
19
20

<tbody>
</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)))))
 
Last edited:
Upvote 0
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
 
Upvote 0
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
ABMN
9DateNameDateName
108/04/2019John8/04/2019John
118/04/2019jacob
12jacob8/04/2019Jill
138/04/2019john
14Jill8/05/2019john
15john8/05/2019jacob
168/05/20198/05/2019jill
17john8/06/2019bill
18jacob8/06/2019joe
198/07/2019john
20jill
21
228/06/2019bill
23joe
24
258/07/2019
26john
27
Find Names Based On Date
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top