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

clairi55a

New Member
Joined
Jan 20, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    182.8 KB · Views: 11

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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).
 
Upvote 0
There might be a better way to do this but you can try this: Put this in AM2
Excel Formula:
=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)),"")
 

Attachments

  • 1674239656887.png
    1674239656887.png
    34.5 KB · Views: 7
Upvote 0
Hi

This presumes that you only want working days

Book1
ABCDEFGH
1AGHADAM
2Machine Profile Name Serial Number Work Orr Work Type: Work Created DateRepeat after how many days? TRUE
32 sampleMM02867sampleUK Repair Medium, 16/09/202216/09/2022TRUE
43 sampleMIM09680sampleUK Repair Medium 16/09/2022FALSE
54 sampleMM07207sampleUK Repair Medium 16/09/2022FALSE
6sampleLK44163sampleUK Repair Medium 16/09/2022FALSE
76 sampleLK28636sampleUK Client Care Love 16/09/2022FALSE
8sample sampleMIM07155sampleUK Preventative M 16/09/2022FALSE
9MIM02063sampleDE Repair Low16/09/2022FALSE
109 sampleLK20455sampleFR Repair Medium 16/09/2022FALSE
1110 sample,2822013331sampleDE Revisit Low16/09/2022FALSE
1211 sample21620391sampleUK RepairLowXX 16/09/2022FALSE
131.2 sampleMIM01982-02i4VsampleUK Repair Low16/09/2022FALSE
1413 sampleMM08528sampleDE Repair High16/09/2022FALSE
1514 sampleMIM08661sampleUK Repair Medium 16/09/2022FALSE
1615 sampleME-02412sampleUK Repair High03/10/2022FALSE
1716 sampleLK27968sampleFR Repair Medium 16/09/2022FALSE
1817 sampleL1022462sampleUK Repair Medium03/10/2022FALSE
1918 sampleLK44242sampleUK Repair Medium 16/09/2022FALSE
2019 sampleLK37865sampleUk Revisit Medium03/10/2022FALSE
2120 sample73519018sampleUK Repair Medium 16/09/2022FALSE
222.1 sampleMM03504sampleUK Repair Medium 14/09/2022FALSE
232.2 sampleMM02939sampleUK Repair Medium 16/09/2022FALSE
242.3 sample MIE04044OsampleFR Installation No (16/09/2022FALSE
25.24 sampleMM02867sampleDE Repair High05/10/202214
26.25 sample'21221936sampleFR Installation No 116/09/2022FALSE
27.26 sample453-083328sampleUK Preventative M 16/09/2022FALSE
282.7 sampleK45014837sampleDE Repair High19/09/2022FALSE
29.28 sampleLK31743sampleFR Repair Medium 16/09/2022FALSE
30.29 sampleK4509006sampleDE Repair Low016/09/2022FALSE
3130 sampleMIM01764sampleUK Repair Medium 16/09/2022FALSE
3231 sampleMM00905sampleUK Preventative M 16/09/2022FALSE
333.2 sample453-083610sampleUK Preventative M 16/09/2022FALSE
Sheet4
Cell Formulas
RangeFormula
H25H25=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
CellConditionCell FormatStop If True
B3:B33Cell ValueduplicatestextNO
 
Upvote 0
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
FADAM
1SNDateDays
2MM0286716/09/2022 
3MIM09680 
4MM07207 
5LK44163 
6LK28636 
7MIM07155 
8MIM0206316/09/2022 
9LK20455 
10,282201333116/09/2022 
1121620391 
12MIM01982-02i4V16/09/2022 
13MM0852816/09/2022 
14MIM08661 
15ME-024123/10/2022 
16LK27968 
17L1022462 
18LK44242 
19LK37865 
2073519018 
21MM03504 
22MM02939 
23MIE04044O 
24MM028675/10/202219
25'21221936 
26453-083328 
27K4501483719/09/2022 
28LK31743 
29K4509006016/09/2022 
30MIM01764 
31MM00905 
32453-083610 
Days elapsed
Cell Formulas
RangeFormula
AM2:AM32AM2=IFERROR(AD2-XLOOKUP(F2,F$1:F1,AD$1:AD1,"",,-1),"")
 
Upvote 0

Forum statistics

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