LOOKUP previous occurences of a value in a column and calculate number of days between rows

[clairi55a]

Hello I hope someone can help me, please
I have a spreadsheet of data, where each row represents a reported machine breakdown and technician visit.
Column F contains the serial number of the machine that has broken down.
Column AD contains the date that the breakdown was reported.
I am trying to create a formula in column AM that looks for the serial number (column F value) in the rest of the rows, and counts how many days have elapsed since the last time it appeared (column AM value).
So far, I have written a formula that tells me if the serial number (F) has been seen before, but I don't know how to then add in something to calculate the days between dates (AM). This is my formula so far... =IFERROR(COUNTIFS(F:F,F2)>1,"")
Can anyone help, please? I've attached an image, as my laptop security won't let me download the app to add a sample spreadsheet. Many Thanks

 

[clairi55a]

Apologies...small error in the sentence below....should have read "counts how many days have elapsed since the last time it appeared (column AD value)."

I am trying to create a formula in column AM that looks for the serial number (column F value) in the rest of the rows, and counts how many days have elapsed since the last time it appeared (column AM value).

 

[shinigamilight]

There might be a better way to do this but you can try this: Put this in AM2

=IF(COUNTIFS($F:$F,F2)=2,DAYS(LARGE(--($F$2:$F$10007=F2)*$AD$2:$AD$10007,1),LARGE(--($F$2:$F$10007=F2)*$AD$2:$AD$10007,2)),"")

 

[Skybluekid]

Hi

This presumes that you only want working days

Book1
	A	B	C	D	E	F	G	H
1	A		G			H	AD	AM
2	Machine Profile Name Serial Number Work Orr Work Type: Work Created Date							Repeat after how many days? TRUE
3	2 sample	MM02867		sample	UK Repair Medium, 16/09/2022		16/09/2022	TRUE
4	3 sample	MIM09680		sample	UK Repair Medium 16/09/2022			FALSE
5	4 sample	MM07207		sample	UK Repair Medium 16/09/2022			FALSE
6	sample	LK44163		sample	UK Repair Medium 16/09/2022			FALSE
7	6 sample	LK28636		sample	UK Client Care Love 16/09/2022			FALSE
8	sample sample	MIM07155		sample	UK Preventative M 16/09/2022			FALSE
9		MIM02063		sample	DE Repair Low		16/09/2022	FALSE
10	9 sample	LK20455		sample	FR Repair Medium 16/09/2022			FALSE
11	10 sample	,2822013331		sample	DE Revisit Low		16/09/2022	FALSE
12	11 sample	21620391		sample	UK RepairLowXX 16/09/2022			FALSE
13	1.2 sample	MIM01982-02i4V		sample	UK Repair Low		16/09/2022	FALSE
14	13 sample	MM08528		sample	DE Repair High		16/09/2022	FALSE
15	14 sample	MIM08661		sample	UK Repair Medium 16/09/2022			FALSE
16	15 sample	ME-02412		sample	UK Repair High		03/10/2022	FALSE
17	16 sample	LK27968		sample	FR Repair Medium 16/09/2022			FALSE
18	17 sample	L1022462		sample	UK Repair Medium03/10/2022			FALSE
19	18 sample	LK44242		sample	UK Repair Medium 16/09/2022			FALSE
20	19 sample	LK37865		sample	Uk Revisit Medium03/10/2022			FALSE
21	20 sample	73519018		sample	UK Repair Medium 16/09/2022			FALSE
22	2.1 sample	MM03504		sample	UK Repair Medium 14/09/2022			FALSE
23	2.2 sample	MM02939		sample	UK Repair Medium 16/09/2022			FALSE
24	2.3 sample	MIE04044O		sample	FR Installation No (16/09/2022			FALSE
25	.24 sample	MM02867		sample	DE Repair High		05/10/2022	14
26	.25 sample	'21221936		sample	FR Installation No 116/09/2022			FALSE
27	.26 sample	453-083328		sample	UK Preventative M 16/09/2022			FALSE
28	2.7 sample	K45014837		sample	DE Repair High		19/09/2022	FALSE
29	.28 sample	LK31743		sample	FR Repair Medium 16/09/2022			FALSE
30	.29 sample	K4509006		sample	DE Repair Low		016/09/2022	FALSE
31	30 sample	MIM01764		sample	UK Repair Medium 16/09/2022			FALSE
32	31 sample	MM00905		sample	UK Preventative M 16/09/2022			FALSE
33	3.2 sample	453-083610		sample	UK Preventative M 16/09/2022			FALSE
Sheet4

Cell Formulas
Range	Formula
H25	H25	=IF(LOOKUP(2^15,SEARCH(B25,$B$3:B24))=1,NETWORKDAYS(XLOOKUP(B25,$B$3:B24,$G$3:G24,,,-1),G25),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B33	Cell Value	duplicates	text	NO

 

[Peter_SSs]

Welcome to the MrExcel board!

I am wondering if it is this?

For the future BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 01 21.xlsm
	F																								AD									AM
1	SN																								Date									Days
2	MM02867																								16/09/2022
3	MIM09680
4	MM07207
5	LK44163
6	LK28636
7	MIM07155
8	MIM02063																								16/09/2022
9	LK20455
10	,2822013331																								16/09/2022
11	21620391
12	MIM01982-02i4V																								16/09/2022
13	MM08528																								16/09/2022
14	MIM08661
15	ME-02412																								3/10/2022
16	LK27968
17	L1022462
18	LK44242
19	LK37865
20	73519018
21	MM03504
22	MM02939
23	MIE04044O
24	MM02867																								5/10/2022									19
25	'21221936
26	453-083328
27	K45014837																								19/09/2022
28	LK31743
29	K4509006																								016/09/2022
30	MIM01764
31	MM00905
32	453-083610
Days elapsed

Cell Formulas
Range	Formula
AM2:AM32	AM2	=IFERROR(AD2-XLOOKUP(F2,F$1:F1,AD$1:AD1,"",,-1),"")