# Finding recent date from list

#### jimmy604

##### New Member
This is my first post so I apologize if my request is easier than I expected. I have been trying to figure this out for a while. I have two columns of data. In column one is simply dates, in column two is the person attending on that date. There are various people who attend on various dates. What I am trying to find is the last time each person attended. Ive used index, max, and all kinds of formulas with no luck.

column 1 Column 2
08-Apr-15 Dan
09-Apr-15 Bill
10-Apr-15 Dan
11-Apr-15 Phil
12-Apr-15 Tom
13-Apr-15 Dan
14-Apr-15 Dustin
15-Apr-15 Dan
16-Apr-15 Phil
17-Apr-15 Bill
18-Apr-15 Dan

With this, when was the last time Dan attended, Bill, Phil and so on. If you can help, I would appreciate it so much.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
 Row\Col A​ B​ C​ 1​ date name last attendance date 2​ 8-Apr-15​ Dan 18-Apr-15​ 3​ 9-Apr-15​ Bill 17-Apr-15​ 4​ 10-Apr-15​ Dan 18-Apr-15​ 5​ 11-Apr-15​ Phil 16-Apr-15​ 6​ 12-Apr-15​ Tom 12-Apr-15​ 7​ 13-Apr-15​ Dan 18-Apr-15​ 8​ 14-Apr-15​ Dustin 14-Apr-15​ 9​ 15-Apr-15​ Dan 18-Apr-15​ 10​ 16-Apr-15​ Phil 16-Apr-15​ 11​ 17-Apr-15​ Bill 17-Apr-15​ 12​ 18-Apr-15​ Dan 18-Apr-15​ 13​

Assuming that dates associated with each name is sequential (entered from earliest to latest)...

C2, copied down:

=LOOKUP(9.99999999999999E+307,1/(\$B\$2:\$B\$12=\$B2),\$A\$2:\$A\$12)

That is great. Hours of stress solved in a flash. Now if this data is on a separate sheet, how do I bring that forward. The formula that you have works if the data is on that page. Mine is on a separate page (ie sheet 2). Thanks again.

That is great. Hours of stress solved in a flash. Now if this data is on a separate sheet, how do I bring that forward. The formula that you have works if the data is on that page. Mine is on a separate page (ie sheet 2). Thanks again.

Just add the sheet prefix to the appropriate ranges...

=LOOKUP(9.99999999999999E+307,1/(Sheet2!\$B\$2:\$B\$12=\$B2),Sheet2!\$A\$2:\$A\$12)

Note. If you really meant "sheet 2", I'd suggest to revert back to the default name Sheet2.

When I add the sheet prefix, I get a 00/01/1900. I use the date format to no avail.

When I add the sheet prefix, I get a 00/01/1900. I use the date format to no avail.

Let's see how the formula looks after what you did to it...

Looking at my post I think where the problem lies is that I am looking for a summary. Using my initial post

column 1 Column 2
08-Apr-15 Dan
09-Apr-15 Bill
10-Apr-15 Dan
11-Apr-15 Phil
12-Apr-15 Tom
13-Apr-15 Dan
14-Apr-15 Dustin
15-Apr-15 Dan
16-Apr-15 Phil
17-Apr-15 Bill
18-Apr-15 Dan

I need to make a second table that obtains the last time each attended. Extracting the data from sheet2

Name Date
Dan
Bill
Phil
tom
Dustin

this might give a better sense of the goal.

Looking at my post I think where the problem lies is that I am looking for a summary. Using my initial post

column 1 Column 2
08-Apr-15 Dan
09-Apr-15 Bill
10-Apr-15 Dan
11-Apr-15 Phil
12-Apr-15 Tom
13-Apr-15 Dan
14-Apr-15 Dustin
15-Apr-15 Dan
16-Apr-15 Phil
17-Apr-15 Bill
18-Apr-15 Dan

I need to make a second table that obtains the last time each attended. Extracting the data from sheet2

Name Date
Dan
Bill
Phil
tom
Dustin

this might give a better sense of the goal.

Let's Sheet3 house the output table, with names in column A from A2 on.

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,1/(Sheet2!\$B\$2:\$B\$12=\$A2),Sheet2!\$A\$2:\$A\$12)

I'm probably doing something wrong here. When I do the formula, I get the 00/01/1900 in the solution. here is the formula

=LOOKUP(9.99999999999999E+307,1/(Logbook!\$A\$6:\$A\$35000=\$A6),Logbook!\$B\$6:\$B\$3500)

Looking at the worksheet, it appears that the formula is highlighting a6 in my current sheet rather than looking to logbook. The data that I want to extract is in logbook. Column A is the date, and column b is the person. I'm trying to extract this data into another sheet to show the most recent time someone attended.

I'm probably doing something wrong here. When I do the formula, I get the 00/01/1900 in the solution. here is the formula

=LOOKUP(9.99999999999999E+307,1/(Logbook!\$A\$6:\$A\$35000=\$A6),Logbook!\$B\$6:\$B\$3500)

Looking at the worksheet, it appears that the formula is highlighting a6 in my current sheet rather than looking to logbook. The data that I want to extract is in logbook. Column A is the date, and column b is the person. I'm trying to extract this data into another sheet to show the most recent time someone attended.

Where did you enter this formula, that is, in which sheet and in which cell of that sheet?

Replies
5
Views
258
Replies
3
Views
191
Replies
2
Views
317
Replies
0
Views
154
Replies
2
Views
282

1,196,430
Messages
6,015,222
Members
441,882
Latest member
rcgyuk

### 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.

### Which adblocker are you using?

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

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