Find date of next change of name for roster

J J N

New Member
Joined
Jul 20, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi All,
New here, I have scoured these forums and others trying to get an answer to my task.
I have a SS that has a date(col a),name(col b) and contact number(col c) that we use as a reference to a roster.
I can extract the name and number based on the date , what I am hoping to do is show the date when the roster changes.
In the example below Fred is on roster today, 20/07/2020, and Dan is to start on 22/07/2020.
On the main roster I want to show that Fred is on roster until 21/07/2020 when Dan takes over.
I have looked at match and index functions but can't get them



20/07/2020​
Fred Bloggs0400 000 111
21/07/2020​
Fred Bloggs0400 000 111
22/07/2020​
Dan Dare0400 111 111
23/07/2020​
Dan Dare0400 111 111
24/07/2020​
Dan Dare0400 111 111
25/07/2020​
Dan Dare0400 111 111
26/07/2020​
Dan Dare0400 111 111
27/07/2020​
Dan Dare0400 111 111
28/07/2020​
Dan Dare0400 111 111
29/07/2020​
Stan Smith0400 222 111
30/07/2020​
Stan Smith0400 222 111
31/07/2020​
Stan Smith0400 222 111
1/08/2020​
Stan Smith0400 222 111
2/08/2020​
Stan Smith0400 222 111
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Welcome to the forum, try

=AGGREGATE(14,6,($A$1:$A$14)/($B$1:$B$14=H3),1)

Change H3 to the cell on Main Roster which contains the Name.
 

J J N

New Member
Joined
Jul 20, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
hi gaz, i tried this but get the dreaded #NUM! , i found help on the aggregate function but cannot understand how what ,($A$1:$A$14)/($B$1:$B$14=H3) is trying to achieve,
is it A1:A14 divided by B1:B14 equals Name (Fred Bloggs ) , and the 1 indicates give me the highest return value ?
Thanks
J
 

J J N

New Member
Joined
Jul 20, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
an update gaz, i put the formula into a copy of my lice spreadsheet, and it returns a numeric value 44196, can you shed any light ?
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
44196 is the date, simply re-format as date and it should be ok.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,145
Messages
5,623,003
Members
415,945
Latest member
Koboca

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
Top