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

#### AndreaG

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

Kind regards
Andrea

 Contract Number Date Raised AP0102 04/11/2010 AP0102 04/11/2010 AP0102 05/01/2011 AP0102 01/03/2011 AP0102 01/03/2011 AP0102 15/03/2011 AP0102 16/03/2011 AP0102 10/06/2011 AP0102 12/07/2011 AP0102 21/07/2011 AP0102 21/07/2011 AP0102 17/02/2012 AP0102 12/03/2012 AP0102 21/09/2012 AP0102 31/01/2011 AP0102 21/07/2011 AP0102 21/07/2011 AP0102 14/10/2011 AP0102 30/03/2012 AP0102 30/11/2012 AP0102 12/12/2012 AP0102 06/12/2013 AP0103 10/05/2011 AP0103 01/08/2011 AP0103 03/10/2011 AP0103 04/10/2011 AP0103 04/10/2011 AP0103 03/11/2011 AP0103 17/02/2012 AP0103 24/05/2012 AP0103 10/05/2011 AP0103 14/10/2011 AP0103 01/02/2012 AP0103 25/11/2011 AP0103 11/02/2011 AP0103 14/07/2011 AP0103 14/10/2011 AP0103 23/02/2011 AP0103 21/09/2011 AP0104 09/06/2011 AP0104 19/07/2011 AP0105 14/10/2011 AP0105 03/11/2011 AP0105 17/11/2011 AP0105 09/03/2011 AP0105 01/09/2011 AP0105 01/09/2011 AP0105 01/09/2011 AP0105 21/07/2011 AP0105 30/03/2011 AP0105 09/06/2011 AP0105 06/07/2011 AP0105 14/10/2011 AP0106 12/04/2012 AP0107 27/04/2012 AP0108 24/05/2012 AP0108 31/05/2012 AP0108 05/10/2012 AP0108 21/02/2013 AP0108 30/09/2013 AP0108 27/02/2014 AP0108 04/11/2014 AP0110 10/11/2014 AP0110 27/11/2014 AP0110 30/03/2015 AP0110 08/04/2015 AP0110 08/05/2015 AP0110 26/04/2011 AP0111 09/09/2011 AP0111 12/05/2011 AP0111 12/05/2011 AP0111 15/07/2011 AP0111 02/09/2011 AP0111 02/09/2011 AP0111 20/12/2016 AP0111 10/02/2017 AP0112 13/09/2017 AP0112 15/12/2017 AP0112 27/04/2011 AP0112 14/10/2011 AP0113 26/07/2012 AP0113 11/12/2012 AP0113 21/02/2013 AP0113 05/06/2013 AP0113 22/07/2014 AP0113 26/07/2011 AP0114 14/06/2011 AP0116 14/07/2011 AP0116 04/08/2011 AP0116 16/09/2011 AP0116 01/12/2011 AP0116 09/12/2011 AP0117 09/12/2011 AP0117 17/02/2012 AP0117 12/03/2012 AP0117 31/05/2012 AP0118 17/12/2012 AP0119 17/12/2012 AP0120 21/05/2013 AP0121 21/05/2013 AP0121 21/07/2011 AP0121 11/08/2011 AP0121 08/09/2011 AP0121 30/09/2011 AP0121 02/03/2012 AP0121 08/05/2012 AP0121 08/05/2012 AP0121 31/05/2012 AP0121 12/06/2012 AP0121 16/08/2012 AP0121 17/05/2013 AP0121 01/08/2011 AP0121 01/08/2011 AP0121 05/04/2012 AP0121 13/04/2012 AP0121 24/05/2012 AP0121 16/08/2012 AP0121 30/11/2012 AP0121 18/01/2013 AP0121 16/08/2013 AP0121 20/08/2013 AP0121 16/08/2011 AP0121 01/09/2011 AP0121 05/09/2011 AP0121 01/09/2011 AP0121 12/03/2012 AP0121 28/03/2012 AP0121 08/06/2012 AP0121 27/09/2012 AP0121 05/10/2012 AP0122 30/11/2012 AP0122 21/02/2013 AP0123 30/07/2013 AP0123 30/07/2013 AP0123 18/10/2013 AP0123 18/10/2013 AP0123 18/10/2013 AP0123 03/10/2011 AP0123 24/08/2011 AP0123 28/10/2011 AP0123 05/03/2012 AP0123 12/03/2012 AP0123 24/05/2012 AP0123 02/07/2012 AP0123 24/08/2012 AP0123 28/09/2012 AP0124 31/12/2012 AP0124 06/09/2013 AP0124 28/02/2014 AP0124 06/03/2014 AP0124 23/05/2017 AP0124 23/09/2011 AP0124 20/12/2011 AP0124 17/01/2012 AP0124 15/11/2011 AP0124 22/11/2011 AP0124 22/11/2011 AP0125 20/09/2013 AP0125 12/03/2014 AP0125 28/08/2014 AP0125 24/10/2014 AP0125 29/10/2014 AP0126 17/12/2014 AP0127 05/01/2015 AP0128 09/02/2015 AP0128 20/02/2015 AP0128 10/04/2015 AP0128 05/06/2015 AP0129 04/03/2016 AP0130 21/10/2011 AP0131 21/03/2012 AP0131 17/11/2011 AP0131 13/01/2012 AP0131 05/04/2012 AP0131 31/05/2012 AP0131 16/08/2012 AP0131 06/09/2012 AP0131 28/09/2012 AP0131 17/04/2013 AP0131 16/10/2013 AP0131 02/12/2013 AP0131 19/12/2013 AP0131 22/11/2011 AP0131 24/08/2012 AP0131 24/09/2012 AP0131 20/12/2013 AP0131 28/04/2014 AP0131 05/11/2014 AP0131 03/12/2014 AP0131 26/01/2015 AP0132 23/11/2011 AP0132 24/11/2011 AP0132 30/05/2012 AP0132 24/10/2012 AP0132 24/10/2012 AP0132 01/12/2011 AP0132 12/03/2012 AP0132 01/12/2011 AP0132 13/01/2012 AP0132 01/02/2012 AP0132 12/03/2012 AP0132 30/05/2012 AP0132 24/08/2012 AP0132 28/09/2012 AP0132 21/02/2013 AP0132 08/03/2013 AP0132 24/07/2013 AP0132 24/07/2013 AP0132 30/08/2013 AP0132 28/04/2014 AP0132 29/04/2014 AP0133 15/03/2016 AP0134 27/04/2016 AP0135 27/04/2016 AP0136 03/03/2017 AP0137 03/03/2017 AP0137 09/03/2017 AP0137 03/08/2017 AP0137 14/09/2017 AP0137 14/09/2017 AP0137 14/09/2017 AP0137 08/12/2011 AP0137 01/02/2012 AP0137 02/03/2012 AP0137 30/03/2012 AP0137 05/04/2012 AP0137 15/06/2012 AP0137 16/08/2012 AP0137 28/09/2012 AP0137 28/11/2012 AP0137 31/12/2012 AP0137 28/03/2013 AP0137 12/07/2013 AP0137 29/10/2014 AP0137 25/01/2013 AP0137 16/12/2011 AP0137 12/03/2012 AP0137 24/05/2012 AP0138 24/08/2012 AP0140 28/09/2012 AP0141 12/12/2012 AP0142 20/02/2013 AP0143 19/04/2013 AP0143 29/07/2013 AP0144 26/11/2013 AP0144 20/12/2013 AP0145 29/01/2014 AP0145 25/03/2014 AP0145 29/04/2014 AP0145 29/04/2014 AP0145 11/06/2014 AP0145 20/12/2011 AP0145 13/01/2012 AP0145 17/02/2012 AP0145 21/03/2012 AP0145 21/12/2011 AP0145 04/01/2012 AP0145 27/02/2012 AP0145 13/03/2012 AP0145 25/09/2012 AP0145 04/02/2013 AP0145 17/03/2014 AP0145 09/01/2012 AP0145 02/02/2012 AP0145 24/05/2012 AP0145 30/05/2012 AP0145 22/06/2012 AP0145 22/10/2012 AP0145 24/01/2013 AP0145 11/02/2013 AP0146 27/03/2013 AP0146 30/04/2013 AP0146 26/06/2013 AP0146 29/07/2013 AP0147 29/01/2014 AP0148 24/03/2014 AP0148 28/04/2014 AP0148 28/08/2014 AP0148 29/08/2014 AP0148 17/10/2014 AP0148 17/10/2014 AP0148 18/01/2012 AP0148 18/01/2012 AP0148 12/03/2012 AP0148 24/05/2012 AP0148 24/05/2012 AP0148 07/11/2012 AP0148 11/12/2012 AP0148 01/05/2013 AP0148 25/10/2013 AP0148 31/10/2013 AP0148 26/11/2013 AP0148 03/01/2014 AP0149 09/09/2014 AP0149 29/02/2012 AP0162 13/09/2012 AP0162 26/01/2012 AP0162 30/03/2012 AP0162 24/05/2012 AP0163 22/06/2012 AP0163 24/01/2012 AP0163 26/07/2012 AP0163 09/10/2012 AP0163 09/10/2012 AP0163 16/11/2012 AP0163 16/11/2012 AP0163 18/01/2013 AP0163 08/02/2013 AP0163 22/03/2013 AP0163 08/01/2016 AP0163 28/03/2012 AP0163 02/02/2012 AP0163 10/10/2012 AP0163 27/03/2013 AP0163 29/02/2012 AP0163 23/03/2013 AP0163 05/04/2013 AP0163 13/03/2012 AP0163 16/04/2012 AP0163 05/10/2012 AP0163 15/10/2012 AP0163 27/03/2013 AP0163 29/07/2013 AP0163 19/03/2012 AP0163 07/11/2012 AP0163 04/04/2014 AP0163 14/09/2012 AP0164 30/03/2012 AP0165 03/04/2012 AP0167 08/05/2012 AP0167 08/05/2012 AP0167 17/06/2012 AP0167 14/05/2012 AP0167 26/04/2012 AP0167 15/06/2012 AP0167 29/06/2012 AP0167 25/07/2012 AP0167 28/09/2012 AP0167 21/02/2013 AP0167 12/03/2015 AP0167 26/07/2016 AP0167 25/11/2016 AP0167 28/11/2016 AP0167 07/12/2016 AP0167 16/01/2017 AP0167 22/09/2017 AP0167 30/05/2012 AP0168 25/07/2012 AP0168 02/05/2012 AP0168 30/05/2012 AP0168 10/05/2012 AP0168 12/12/2012 AP0168 29/07/2013 AP0168 30/09/2013 AP0168 28/04/2014 AP0168 22/07/2014 AP0168 30/11/2012 AP0168 30/04/2013 AP0168 10/10/2013 AP0168 20/12/2013 AP0168 17/01/2014 AP0169 30/05/2014 AP0170 11/06/2014 AP0170 18/06/2014 AP0170 18/06/2014 AP0170 23/07/2014 AP0171 22/08/2014 AP0171 08/10/2014 AP0171 08/10/2014 AP0172 04/11/2014 AP0172 05/11/2014 AP0172 13/11/2014 AP0172 27/11/2014 AP0172 10/12/2014 AP0172 23/02/2015 AP0172 11/05/2012 AP0172 27/07/2012 AP0172 17/12/2012 AP0172 29/07/2013 AP0172 31/10/2013 AP0172 27/02/2014 AP0172 16/09/2014 AP0172 15/10/2014 AP0172 05/11/2014 AP0172 22/04/2015 AP0172 13/07/2015 AP0172 15/06/2012 AP0172 27/07/2012 AP0172 16/08/2012 AP0172 16/08/2012 AP0172 10/09/2012 AP0172 26/06/2013 AP0172 29/07/2013 AP0172 31/10/2013 AP0172 26/11/2013 AP0172 22/01/2014 AP0172 04/06/2014 AP0172 24/07/2014 AP0172 22/08/2014 AP0172 08/09/2014 AP0172 25/01/2017 AP0172 15/10/2014 AP0172 20/10/2014 AP0172 19/01/2015 AP0172 06/10/2015 AP0172 15/06/2012 AP0172 27/07/2012 AP0172 15/06/2012 AP0173 18/03/2013 AP0173 10/10/2013 AP0173 31/10/2013 AP0173 06/12/2013 AP0173 22/06/2012 AP0173 10/09/2012 AP0173 16/10/2012 AP0173 25/10/2012 AP0173 23/01/2013 AP0173 28/02/2013 AP0173 08/03/2013 AP0173 30/04/2013 AP0173 17/05/2013 AP0173 06/09/2013 AP0173 26/09/2013 AP0173 26/09/2013 AP0173 06/12/2013 AP0173 03/01/2014 AP0173 28/02/2014 AP0173 20/07/2012 AP0173 20/07/2012 AP0173 17/07/2012 AP0173 26/07/2012 AP0173 17/09/2012 AP0174 26/10/2012 AP0175 31/10/2012 AP0175 31/10/2012 AP0175 30/11/2012 AP0175 07/12/2012 AP0176 31/12/2012 AP0176 31/01/2013 AP0176 28/02/2013 AP0176 28/06/2013 AP0176 29/07/2013 AP0176 17/12/2013 AP0176 13/06/2014

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

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

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Eric W

##### MrExcel MVP

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

</tbody>
Sheet4

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

</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))}

</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>

#### AndreaG

##### New Member
Perfect - many thanks

Replies
4
Views
49
Replies
3
Views
55
Replies
17
Views
109
Replies
3
Views
138
Replies
8
Views
90