Flag first date in a series from a database

NewUser2

Board Regular
Joined
Jan 27, 2015
Messages
54
Hi, need some help. Below is an example of my data which comes from a very large database. Column B is a date that can be when the client renewed their terms with us (if the client is listed more than once) or originally signed with us (if listed just once). I want to put a formula in column C that will flag which row is the earliest date on file with that client. It's probably a simple IF(AND(MIN type of function, but it's not coming to me... Any suggestions?

If my data was nicely organized as shown below it would be easy to just flag the first mention of the client ID #. But it isn't.

A B
1 Client Date
2 111 01/1/2016
3 111 01/1/2017
4 111 01/1/2018
5 122 01/1/2016
6 122 02/23/2017
7 122 06/1/2017
8 444 5/1/2017
9 445 9/1/2018
10 446 10/1/2015
11 446 01/1/2016
12 446 08/1/2016
13 446 01/1/2017
14 446 10/1/2018
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hello,

I added oldest and newest in this example using MIN and MAX:

Excel Workbook
ABCDE
1ClientDateClient111
21111/1/2016Oldest Date1/1/2016
31111/1/2017Newest Date1/1/2018
41111/1/2018
51221/1/2016
61222/23/2017
71226/1/2017
84445/1/2017
94459/1/2018
1044610/1/2015
114461/1/2016
124468/1/2016
134461/1/2017
1444610/1/2018
Sheet1
 
Upvote 0
Perfect, thanks!




Hello,

I added oldest and newest in this example using MIN and MAX:

Sheet1

ABCDE
1ClientDate Client 111
21111/1/2016 Oldest Date1/1/2016
31111/1/2017 Newest Date1/1/2018
41111/1/2018
51221/1/2016
61222/23/2017
71226/1/2017
84445/1/2017
94459/1/2018
1044610/1/2015
114461/1/2016
124468/1/2016
134461/1/2017
1444610/1/2018

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:85.6px;"><col style="width:85.6px;"><col style="width:28px;"><col style="width:105.6px;"><col style="width:85.6px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E2{=MIN(IF(A2:A14=E1,B2:B14))}
E3{=MAX(IF(A2:A14=E1,B2:B14))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
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