Find matching variable in one column and then run date difference function according to the matching rows

[filmfanatic1970]

I'm not sure if this can be done, but I am trying to get the day difference between two dates. The catch is that I'm looking to match multiple text in the first column and then run a day difference function for the first two matches...and then the next two matches, etc. through the entire sheet.

There are three variables in column A: P1, P2 and P3.

In the example below, I want to match the first P3 and then the next. Then, grab the two dates for the first match in column C and run a day difference function (In D5, I put =DAYS(D5,D3), because A5 and A3 match. It comes up with 2.0. Then I want to match the next P3, after the one in A5, which is A9. The day difference is shown as 5.0.

I'm trying to do the same thing for all P1, P2 and P3 matches, all the way down column A. Going forward, as an example, if I were to type in a P2 in A13 and put a date in column C, then it would match with the previous P2 in A8 and calculate the day difference between row 8 and row 13.

I don't think this can be done with traditional formulas and was thinking it needs to be done with VBA.

I hope this makes sense.

 

[KRice]

Welcome to the Board! There are several ways to do this with formulas. Here is one approach that works with Excel 365. I don't think you want 0 returned for MT to Failure if a match is not found above, as that will skew your average. In this example, I've left unmatched entries as blanks, which are then ignored by the AVERAGE function:

MrExcel_20220313.xlsx
	A	B	C	D	E
1				47.875
2	Level	Title	Initial/DateTime	MT To Failure	EndDateTime
3	P3	Title1	2/2/2022 11:10		2/2/2022
4	P2	Title2	2/2/2022 13:05		2/3/2022
5	P3	Title3	2/4/2022 14:10	2	2/7/2022
6	P2	Title4	2/4/2022 15:22	2	2/7/2022
7	P2	Title5	2/6/2022 22:15	2	2/7/2022
8	P2	Title6	2/7/2022 8:50	1	2/15/2022
9	P3	Title7	2/9/2022 11:35	5	2/10/2022
10	P3	Title8	2/11/2022 15:35	2	2/15/2022
11	P3	Title9	2/15/2022 12:15	4	2/15/2022
12	P1	Title10	3/7/2022 7:30		3/7/2022
13	P2	Title11	2/7/2023	365
film

Cell Formulas
Range	Formula
D1	D1	=AVERAGE(D3:D13)
D3:D13	D3	=IFERROR(DAYS(C3,XLOOKUP(A3,A$2:A2,C$2:C2,"",0,-1)),"")

 

[filmfanatic1970]

Hi Kirk,

Thanks for the response.

I put the formulas in the corresponding cells and dragged D3 all the way down. The formulas don't seem to be working though. I used the XL2BB add-in and created a mini sheet. I pasted it below. Not sure if it came out correctly. If not, there is a screen shot below it showing what I see.

Incident Management Spreadsheet 2022_V3.0.xlsx
	A	B	C	D	E
1				#DIV/0!
2	Level	Title	InitialDateTime	MT To Failure	EndDateTime
3	P3	Title 1	2/2/2022 11:10		2/2/2022 18:00
4	P2	Title 2	2/2/2022 13:05		2/3/2022 8:50
5	P3	Title 2	2/4/2022 14:10		2/7/2022 10:25
6	P2		2/4/2022 15:22		2/7/2022 11:41
7	P2		2/6/2022 22:15		2/7/2022 11:00
8	P2		2/7/2022 8:50		2/15/2022 17:00
9	P3		2/9/2022 11:35		2/10/2022 10:44
10	P3		2/11/2022 15:35		2/15/2022 9:33
11	P3		2/15/2022 12:15		2/15/2022 15:15
12	P1		3/7/2022 7:30		3/7/2022 10:56
MAIN

Cell Formulas
Range	Formula
D1	D1	=AVERAGE(D3:D13)
D3:D12	D3	=IFERROR(DAYS(C3,XLOOKUP(A3,A$2:A2,C$2:C2,"",0,-1)),"")

Named Ranges
Name	Refers To	Cells
query__1	=MAIN!$A$2:$L$500	D3:D12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A9,J501:L501,I502:L506,B3:G3,I3:XFD3,B4:C9,I4:J10,J11:J500,L101:L500,L4:XFD100,K4:K500,A507:L900,A501:G506,A10:C500,D4:G500	Expression	=$A3="P1"	text	NO

 

[KRice]

Are you using Excel 365? XLOOKUP is a function available in 365, but not earlier versions of Excel. If you're using an earlier verion of Excel, try this:

MrExcel_20220313.xlsx
	A	B	C	D	E
1				47.875
2	Level	Title	Initial/DateTime	MT To Failure	EndDateTime
3	P3	Title1	2/2/2022 11:10		2/2/2022
4	P2	Title2	2/2/2022 13:05		2/3/2022
5	P3	Title3	2/4/2022 14:10	2	2/7/2022
6	P2	Title4	2/4/2022 15:22	2	2/7/2022
7	P2	Title5	2/6/2022 22:15	2	2/7/2022
8	P2	Title6	2/7/2022 8:50	1	2/15/2022
9	P3	Title7	2/9/2022 11:35	5	2/10/2022
10	P3	Title8	2/11/2022 15:35	2	2/15/2022
11	P3	Title9	2/15/2022 12:15	4	2/15/2022
12	P1	Title10	3/7/2022 7:30		3/7/2022
13	P2	Title11	2/7/2023	365
film (2)

Cell Formulas
Range	Formula
D1	D1	=AVERAGE(D3:D13)
D3:D13	D3	=IFERROR(DAYS(C3,LOOKUP(2,1/(A$2:A2=A3),C$2:C2)),"")

 

[filmfanatic1970]

Ah....makes sense. Yes, we're using Office 2016 (moving soon to the cloud and Microsoft 365).

I think we are getting somewhere. I put the new formula in there, but still not getting the results in column D. See below. Seems like =AVERAGE(D3:D13) in D1 isn't working, correct?

 

[KRice]

Can you post the mini-sheet produced by XL2BB so that I can see more details? I'm puzzled by the "FALSE" results in column D.

 

[filmfanatic1970]

Sorry about that. Here you go.

Incident Management Spreadsheet 2022_V4.0.xlsx
	A	B	C	D
1				#DIV/0!
2	Level	Title	InitialDateTime	MT To Failure
3	P3	Title 1	2/2/2022 11:10	FALSE
4	P2	Title 2	2/2/2022 13:05	FALSE
5	P3	Title 2	2/4/2022 14:10	FALSE
6	P2		2/4/2022 15:22	FALSE
7	P2		2/6/2022 22:15	FALSE
8	P2		2/7/2022 8:50	FALSE
9	P3		2/9/2022 11:35	FALSE
10	P3		2/11/2022 15:35	FALSE
11	P3		2/15/2022 12:15	FALSE
12	P1		3/7/2022 7:30	FALSE
MAIN

Cell Formulas
Range	Formula
D1	D1	=AVERAGE(D3:D500)
D3:D12	D3	=IFERROR(DAYS(C3,LOOKUP(2,1/(A$2:A2=A3),C$2:C2)),"") =IFERROR(DAYS(C3,LOOKUP(2,1/(A$2:A2=A3),C$2:C2)),"") =IFERROR(DAYS(C3,LOOKUP(2,1/(A$2:A2=A3),C$2:C2)),"") =IFERROR(DAYS(C3,LOOKUP(2,1/(A$2:A2=A3),C$2:C2)),"")

Named Ranges
Name	Refers To	Cells
query__1	=MAIN!$A$2:$F$500	D3:D12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A9,B4:C9,A10:C500,D4:F500,A501:F900,G4:XFD100,B3:XFD3	Expression	=$A3="P1"	text	NO

 

[KRice]

It appears that you've pasted the formula numerous times into each cell.

 

[filmfanatic1970]

That was it! Thanks!