Latest, 1st Last, 2nd Last entry

[SteffanPE]

Hi,

i'm looking to use a formula / VBA that can show me the latest entry.

Column A is a Unique property Reference, and column F are the job numbers for each job. (highest number = Latest entry).

i'm looking to add a column that will show the latest entry against each property.

i can easily do it in Power BI -

= Calculate(Max(F:F),ALLEXCEPT(A:A))

I need this equivilent in excel if possible.

any help, much appreciated.

Thank you,

A = T1_UPRN	T1_AssetType	T1_VisitDue	T1_VisitActual	T1_VisitOutcome	F = T1_JobNumber
10010040​	House	08/02/2020​	22/11/2019​	Complete	62458​
10010040​	House	18/04/2019​	08/02/2019​	Complete	54843​
10010040​	House	30/05/2018​	18/04/2018​	Complete	45445​
10010060​	House	09/04/2020​	05/02/2020​	Complete	65362​
10010060​	House	08/06/2019​	09/04/2019​	Complete	54844​
10010060​	House	10/07/2018​	08/06/2018​	Complete	45446​
10010070​	House	01/11/2019​	02/09/2019​	Complete	62459​
10010080​	House	29/01/2020​	11/11/2019​	Complete	62460​
10010080​	House	15/03/2019​	29/01/2019​	Complete	54846​
10010080​	House	18/04/2018​	15/03/2018​	Complete	45448​

 

[jasonb75]

Assuming first entry in row 2, see if this does what you need

=MAXIFS(F:F,A:A,A2)

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	A = T1_UPRN	T1_AssetType	T1_VisitDue	T1_VisitActual	T1_VisitOutcome	F = T1_JobNumber
2	10010040	House	02/08/2020	22/11/2019	Complete	62458	Latest
3	10010040	House	18/04/2019	02/08/2019	Complete	54843
4	10010040	House	30/05/2018	18/04/2018	Complete	45445
5	10010060	House	04/09/2020	02/05/2020	Complete	65362	Latest
6	10010060	House	06/08/2019	04/09/2019	Complete	54844
7	10010060	House	07/10/2018	06/08/2018	Complete	45446
8	10010070	House	11/01/2019	09/02/2019	Complete	62459	Latest
9	10010080	House	29/01/2020	11/11/2019	Complete	62460	Latest
10	10010080	House	15/03/2019	29/01/2019	Complete	54846
11	10010080	House	18/04/2018	15/03/2018	Complete	45448
Result

Cell Formulas
Range	Formula
G2:G11	G2	=IF(F2=MAXIFS(F:F,A:A,A2),"Latest","")

 

[SteffanPE]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	A = T1_UPRN	T1_AssetType	T1_VisitDue	T1_VisitActual	T1_VisitOutcome	F = T1_JobNumber
2	10010040	House	02/08/2020	22/11/2019	Complete	62458	Latest
3	10010040	House	18/04/2019	02/08/2019	Complete	54843
4	10010040	House	30/05/2018	18/04/2018	Complete	45445
5	10010060	House	04/09/2020	02/05/2020	Complete	65362	Latest
6	10010060	House	06/08/2019	04/09/2019	Complete	54844
7	10010060	House	07/10/2018	06/08/2018	Complete	45446
8	10010070	House	11/01/2019	09/02/2019	Complete	62459	Latest
9	10010080	House	29/01/2020	11/11/2019	Complete	62460	Latest
10	10010080	House	15/03/2019	29/01/2019	Complete	54846
11	10010080	House	18/04/2018	15/03/2018	Complete	45448
Result

Cell Formulas
Range	Formula
G2:G11	G2	=IF(F2=MAXIFS(F:F,A:A,A2),"Latest","")

Great, Thank you very much.

worked a treat

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

In your sample data, for each property, the latest entry is at the top. So if that data is representative of your actual data, all you would need is ..

20 04 21.xlsm
	A	B	C	D	E	F	G
1	A = T1_UPRN	T1_AssetType	T1_VisitDue	T1_VisitActual	T1_VisitOutcome	F = T1_JobNumber	Latest?
2	10010040	House	2/08/2020	22/11/2019	Complete	62458	Latest
3	10010040	House	18/04/2019	2/08/2019	Complete	54843
4	10010040	House	30/05/2018	18/04/2018	Complete	45445
5	10010060	House	4/09/2020	2/05/2020	Complete	65362	Latest
6	10010060	House	6/08/2019	4/09/2019	Complete	54844
7	10010060	House	7/10/2018	6/08/2018	Complete	45446
8	10010070	House	11/01/2019	9/02/2019	Complete	62459	Latest
9	10010080	House	29/01/2020	11/11/2019	Complete	62460	Latest
10	10010080	House	15/03/2019	29/01/2019	Complete	54846
11	10010080	House	18/04/2018	15/03/2018	Complete	45448
Latest

Cell Formulas
Range	Formula
G2:G11	G2	=IF(A2=A1,"","Latest")