Find the last occurrence, and return a value, and count number of occurances

AndreaG

New Member
Joined
May 14, 2015
Messages
23
Hi
I have attached below a sample of the data I am trying to analyse. Could anyone recommend a formula (or 3) which would return the date of the first occurrence of the text in column A, the last occurrence of the same, and the number of occurrences in total? They are sorted in the alpha numeric sequence. The ultimate aim is to find the length of time (months) between the first and last occurrence, and the number of occurrence for each contract number.

Many thanks in advance.

Kind regards
Andrea

Contract NumberDate Raised
AP010204/11/2010
AP010204/11/2010
AP010205/01/2011
AP010201/03/2011
AP010201/03/2011
AP010215/03/2011
AP010216/03/2011
AP010210/06/2011
AP010212/07/2011
AP010221/07/2011
AP010221/07/2011
AP010217/02/2012
AP010212/03/2012
AP010221/09/2012
AP010231/01/2011
AP010221/07/2011
AP010221/07/2011
AP010214/10/2011
AP010230/03/2012
AP010230/11/2012
AP010212/12/2012
AP010206/12/2013
AP010310/05/2011
AP010301/08/2011
AP010303/10/2011
AP010304/10/2011
AP010304/10/2011
AP010303/11/2011
AP010317/02/2012
AP010324/05/2012
AP010310/05/2011
AP010314/10/2011
AP010301/02/2012
AP010325/11/2011
AP010311/02/2011
AP010314/07/2011
AP010314/10/2011
AP010323/02/2011
AP010321/09/2011
AP010409/06/2011
AP010419/07/2011
AP010514/10/2011
AP010503/11/2011
AP010517/11/2011
AP010509/03/2011
AP010501/09/2011
AP010501/09/2011
AP010501/09/2011
AP010521/07/2011
AP010530/03/2011
AP010509/06/2011
AP010506/07/2011
AP010514/10/2011
AP010612/04/2012
AP010727/04/2012
AP010824/05/2012
AP010831/05/2012
AP010805/10/2012
AP010821/02/2013
AP010830/09/2013
AP010827/02/2014
AP010804/11/2014
AP011010/11/2014
AP011027/11/2014
AP011030/03/2015
AP011008/04/2015
AP011008/05/2015
AP011026/04/2011
AP011109/09/2011
AP011112/05/2011
AP011112/05/2011
AP011115/07/2011
AP011102/09/2011
AP011102/09/2011
AP011120/12/2016
AP011110/02/2017
AP011213/09/2017
AP011215/12/2017
AP011227/04/2011
AP011214/10/2011
AP011326/07/2012
AP011311/12/2012
AP011321/02/2013
AP011305/06/2013
AP011322/07/2014
AP011326/07/2011
AP011414/06/2011
AP011614/07/2011
AP011604/08/2011
AP011616/09/2011
AP011601/12/2011
AP011609/12/2011
AP011709/12/2011
AP011717/02/2012
AP011712/03/2012
AP011731/05/2012
AP011817/12/2012
AP011917/12/2012
AP012021/05/2013
AP012121/05/2013
AP012121/07/2011
AP012111/08/2011
AP012108/09/2011
AP012130/09/2011
AP012102/03/2012
AP012108/05/2012
AP012108/05/2012
AP012131/05/2012
AP012112/06/2012
AP012116/08/2012
AP012117/05/2013
AP012101/08/2011
AP012101/08/2011
AP012105/04/2012
AP012113/04/2012
AP012124/05/2012
AP012116/08/2012
AP012130/11/2012
AP012118/01/2013
AP012116/08/2013
AP012120/08/2013
AP012116/08/2011
AP012101/09/2011
AP012105/09/2011
AP012101/09/2011
AP012112/03/2012
AP012128/03/2012
AP012108/06/2012
AP012127/09/2012
AP012105/10/2012
AP012230/11/2012
AP012221/02/2013
AP012330/07/2013
AP012330/07/2013
AP012318/10/2013
AP012318/10/2013
AP012318/10/2013
AP012303/10/2011
AP012324/08/2011
AP012328/10/2011
AP012305/03/2012
AP012312/03/2012
AP012324/05/2012
AP012302/07/2012
AP012324/08/2012
AP012328/09/2012
AP012431/12/2012
AP012406/09/2013
AP012428/02/2014
AP012406/03/2014
AP012423/05/2017
AP012423/09/2011
AP012420/12/2011
AP012417/01/2012
AP012415/11/2011
AP012422/11/2011
AP012422/11/2011
AP012520/09/2013
AP012512/03/2014
AP012528/08/2014
AP012524/10/2014
AP012529/10/2014
AP012617/12/2014
AP012705/01/2015
AP012809/02/2015
AP012820/02/2015
AP012810/04/2015
AP012805/06/2015
AP012904/03/2016
AP013021/10/2011
AP013121/03/2012
AP013117/11/2011
AP013113/01/2012
AP013105/04/2012
AP013131/05/2012
AP013116/08/2012
AP013106/09/2012
AP013128/09/2012
AP013117/04/2013
AP013116/10/2013
AP013102/12/2013
AP013119/12/2013
AP013122/11/2011
AP013124/08/2012
AP013124/09/2012
AP013120/12/2013
AP013128/04/2014
AP013105/11/2014
AP013103/12/2014
AP013126/01/2015
AP013223/11/2011
AP013224/11/2011
AP013230/05/2012
AP013224/10/2012
AP013224/10/2012
AP013201/12/2011
AP013212/03/2012
AP013201/12/2011
AP013213/01/2012
AP013201/02/2012
AP013212/03/2012
AP013230/05/2012
AP013224/08/2012
AP013228/09/2012
AP013221/02/2013
AP013208/03/2013
AP013224/07/2013
AP013224/07/2013
AP013230/08/2013
AP013228/04/2014
AP013229/04/2014
AP013315/03/2016
AP013427/04/2016
AP013527/04/2016
AP013603/03/2017
AP013703/03/2017
AP013709/03/2017
AP013703/08/2017
AP013714/09/2017
AP013714/09/2017
AP013714/09/2017
AP013708/12/2011
AP013701/02/2012
AP013702/03/2012
AP013730/03/2012
AP013705/04/2012
AP013715/06/2012
AP013716/08/2012
AP013728/09/2012
AP013728/11/2012
AP013731/12/2012
AP013728/03/2013
AP013712/07/2013
AP013729/10/2014
AP013725/01/2013
AP013716/12/2011
AP013712/03/2012
AP013724/05/2012
AP013824/08/2012
AP014028/09/2012
AP014112/12/2012
AP014220/02/2013
AP014319/04/2013
AP014329/07/2013
AP014426/11/2013
AP014420/12/2013
AP014529/01/2014
AP014525/03/2014
AP014529/04/2014
AP014529/04/2014
AP014511/06/2014
AP014520/12/2011
AP014513/01/2012
AP014517/02/2012
AP014521/03/2012
AP014521/12/2011
AP014504/01/2012
AP014527/02/2012
AP014513/03/2012
AP014525/09/2012
AP014504/02/2013
AP014517/03/2014
AP014509/01/2012
AP014502/02/2012
AP014524/05/2012
AP014530/05/2012
AP014522/06/2012
AP014522/10/2012
AP014524/01/2013
AP014511/02/2013
AP014627/03/2013
AP014630/04/2013
AP014626/06/2013
AP014629/07/2013
AP014729/01/2014
AP014824/03/2014
AP014828/04/2014
AP014828/08/2014
AP014829/08/2014
AP014817/10/2014
AP014817/10/2014
AP014818/01/2012
AP014818/01/2012
AP014812/03/2012
AP014824/05/2012
AP014824/05/2012
AP014807/11/2012
AP014811/12/2012
AP014801/05/2013
AP014825/10/2013
AP014831/10/2013
AP014826/11/2013
AP014803/01/2014
AP014909/09/2014
AP014929/02/2012
AP016213/09/2012
AP016226/01/2012
AP016230/03/2012
AP016224/05/2012
AP016322/06/2012
AP016324/01/2012
AP016326/07/2012
AP016309/10/2012
AP016309/10/2012
AP016316/11/2012
AP016316/11/2012
AP016318/01/2013
AP016308/02/2013
AP016322/03/2013
AP016308/01/2016
AP016328/03/2012
AP016302/02/2012
AP016310/10/2012
AP016327/03/2013
AP016329/02/2012
AP016323/03/2013
AP016305/04/2013
AP016313/03/2012
AP016316/04/2012
AP016305/10/2012
AP016315/10/2012
AP016327/03/2013
AP016329/07/2013
AP016319/03/2012
AP016307/11/2012
AP016304/04/2014
AP016314/09/2012
AP016430/03/2012
AP016503/04/2012
AP016708/05/2012
AP016708/05/2012
AP016717/06/2012
AP016714/05/2012
AP016726/04/2012
AP016715/06/2012
AP016729/06/2012
AP016725/07/2012
AP016728/09/2012
AP016721/02/2013
AP016712/03/2015
AP016726/07/2016
AP016725/11/2016
AP016728/11/2016
AP016707/12/2016
AP016716/01/2017
AP016722/09/2017
AP016730/05/2012
AP016825/07/2012
AP016802/05/2012
AP016830/05/2012
AP016810/05/2012
AP016812/12/2012
AP016829/07/2013
AP016830/09/2013
AP016828/04/2014
AP016822/07/2014
AP016830/11/2012
AP016830/04/2013
AP016810/10/2013
AP016820/12/2013
AP016817/01/2014
AP016930/05/2014
AP017011/06/2014
AP017018/06/2014
AP017018/06/2014
AP017023/07/2014
AP017122/08/2014
AP017108/10/2014
AP017108/10/2014
AP017204/11/2014
AP017205/11/2014
AP017213/11/2014
AP017227/11/2014
AP017210/12/2014
AP017223/02/2015
AP017211/05/2012
AP017227/07/2012
AP017217/12/2012
AP017229/07/2013
AP017231/10/2013
AP017227/02/2014
AP017216/09/2014
AP017215/10/2014
AP017205/11/2014
AP017222/04/2015
AP017213/07/2015
AP017215/06/2012
AP017227/07/2012
AP017216/08/2012
AP017216/08/2012
AP017210/09/2012
AP017226/06/2013
AP017229/07/2013
AP017231/10/2013
AP017226/11/2013
AP017222/01/2014
AP017204/06/2014
AP017224/07/2014
AP017222/08/2014
AP017208/09/2014
AP017225/01/2017
AP017215/10/2014
AP017220/10/2014
AP017219/01/2015
AP017206/10/2015
AP017215/06/2012
AP017227/07/2012
AP017215/06/2012
AP017318/03/2013
AP017310/10/2013
AP017331/10/2013
AP017306/12/2013
AP017322/06/2012
AP017310/09/2012
AP017316/10/2012
AP017325/10/2012
AP017323/01/2013
AP017328/02/2013
AP017308/03/2013
AP017330/04/2013
AP017317/05/2013
AP017306/09/2013
AP017326/09/2013
AP017326/09/2013
AP017306/12/2013
AP017303/01/2014
AP017328/02/2014
AP017320/07/2012
AP017320/07/2012
AP017317/07/2012
AP017326/07/2012
AP017317/09/2012
AP017426/10/2012
AP017531/10/2012
AP017531/10/2012
AP017530/11/2012
AP017507/12/2012
AP017631/12/2012
AP017631/01/2013
AP017628/02/2013
AP017628/06/2013
AP017629/07/2013
AP017617/12/2013
AP017613/06/2014

<colgroup><col span="2"></colgroup><tbody>
</tbody>

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>
 

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.
How about 5 formulas?

ABCDEFGH
1Contract NumberDate RaisedContractFirst DateLast DateNumber of months betweenNumber of occurences
2AP010211/4/2010AP010211/4/201012/6/20133722
3AP010211/4/2010AP01032/11/20115/24/20121517
4AP01021/5/2011AP01046/9/20117/19/201112
5AP01023/1/2011AP01053/9/201111/17/2011812
6AP01023/1/2011AP01064/12/20124/12/201201
7AP01023/15/2011AP01074/27/20124/27/201201
8AP01023/16/2011AP01085/24/201211/4/2014297
9AP01026/10/2011AP01104/26/20115/8/2015486
10AP01027/12/2011AP01115/12/20112/10/2017688
11AP01027/21/2011AP01124/27/201112/15/2017794

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
G2=DATEDIF(E2,F2,"m")
H2=COUNTIF(A:A,D2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D2{=IFERROR(INDEX($A:$A,SMALL(IF(MATCH($A$2:$A$445,$A$2:$A$445,0)=ROW($A$2:$A$445)-ROW($A$2)+1,ROW($A$2:$A$445)),ROWS($D$2:$D2))),"")}
E2{=MIN(IF($A$2:$A$445=D2,$B$2:$B$445))}
F2{=MAX(IF($A$2:$A$445=D2,$B$2:$B$445))}

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

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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