Massive Data Analysis needing a helper column

[Noxqss38242]

2021 YTD Data.Master.Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
4504	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	PM-ANN	99	NA	01	DUE	3.40	187.00	244.10	$431.10			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		PM ANN
4505	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	14	FRAME	07	5THWHL	001	ASMBLY	01	ADJ	99	NA	21	OUTADJ	0.10	5.50	0.00	$5.50			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		ADJUST FIFTH WHEEL
4506	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	32	CRNKSY	01	BA/CAB	001	BATT	26	RECHRG	04	QTY 4	43	WEAK	0.20	11.00	0.00	$11.00			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		JUMP START AND PUT ON CHARGER
4507	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	32	CRNKSY	01	BA/CAB	001	BATT	03	RPLNEW	04	QTY 4	43	WEAK	1.40	77.00	0.04	$77.04			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		TEST AND REPLACE 4 BATTERIES
4508	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	AC	ACCID	04	AERODY	05	SKIRTS	001	FRONT	24	REPAIR	22	LEFT	12	BROKEN	2.20	121.00	137.88	$258.88			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		REPAIR STEP BRACKETS FOR BATTERY COVER
4509	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	DE	DRVERR	15	STEERG	01	WHEEL	001	ST WHL	24	REPAIR	99	NA	12	BROKEN	0.10	5.50	89.16	$94.66			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		R R CITY HORN PAD
4510	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	52	ELACCS	05	CB	003	CBWIRE	19	WIRE	99	NA	46	MISSNG	0.20	11.00	6.59	$17.59			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		R R CB POWER CABLE
4511	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	43	EXHSYS	05	DPF	001	FILTER	55	REGEN	99	NA	01	DUE	1.80	99.00	0.00	$99.00			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		REGEN
4512	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	01	HVAC	02	AC CAB	261	CONTRL	09	DIANOS	99	NA	00	NOFAIL	0.30	16.50	0.00	$16.50			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		CHECK MAX AC INOP BLOWS WEAK NOT VERY COLD
4513	TRAC	16869	413,450	414,056	16700--16899	04/18/21	42	28 Apr 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,415,815	S	0016869	01	2018	INTL	10/10/17	C1	LT		CSA
4514	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	001	RPL 1	04	RETRED					0.00	0.00	0.00	$180.00			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		RRI
4515	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	999	OTHER	71	SCRAP	01	QTY 1	12	BROKEN	0.00	0.00	0.00	$10.00			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		SCRAP
4516	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	14	FRAME	09	MUDFLP	003	BRACKT	03	RPLNEW	23	RIGHT	12	BROKEN	0.00	0.00	0.00	$100.50			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		R M F BRKT
4517	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	14	FRAME	09	MUDFLP	001	MUDFLP	03	RPLNEW	23	RIGHT	49	CUT	0.00	0.00	0.00	$37.49			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		R M F
4518	TRAC	16870	353,493	353,493	16700--16899	01/07/21	39	25 Jan 21	Vend	RO	ROAD REPAIR	MA	MAINT	00	PWRUNT	00	TRAC	001	UNIT	64	MOTEL	99	NA	88	RECALL	0.00	0.00	0.00	$161.50			3,391,449	R	1207900	01	2018	INTL	10/10/17	C1	LT		COMPLAINT: MOTEL ROOM NIGHT 1 2
4519	TRAC	16870	353,522	353,493	16700--16899	01/06/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		CSA
4520	TRAC	16870	353,522	353,493	16700--16899	01/06/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	PM-ANN	99	NA	01	DUE	3.60	198.00	213.45	$411.45	PTL WM	PTL - West Memphis	3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		PM ANN
4521	TRAC	16870	353,522	353,493	16700--16899	01/07/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	13	BRKTRA	02	DRVAXL	019	BRKCHA	03	RPLNEW	10	R REAR	12	BROKEN	3.00	165.00	62.48	$227.48			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		RR BRAKE CHAMBER BROKEN
4522	TRAC	16870	353,522	353,493	16700--16899	01/06/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	45	ENGINE	10	O DIST	002	FILCAP	03	RPLNEW	99	NA	44	WORN	0.20	11.00	52.83	$63.83			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		REPLACE OIL CAP
4523	TRAC	16870	353,522	353,493	16700--16899	01/07/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	13	BRKTRA	07	LINES	010	SPRING	01	ADJ	99	NA	21	OUTADJ	0.30	16.50	0.00	$16.50			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		AIRLINE NEED ADJ
4524	TRAC	16870	353,522	353,493	16700--16899	01/07/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	04	AERODY	05	SKIRTS	003	REAR	24	REPAIR	22	LEFT	19	LOOSE	0.60	33.00	0.00	$33.00			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		SIDESKIRT LOOSE
4525	TRAC	16870	0	353,493	16700--16899	01/09/21	39	25 Jan 21	Vend	WM	WEST MEMPHIS	MA	MAINT	32	CRNKSY	01	BA/CAB	010	LVD	24	REPAIR	99	NA	88	RECALL	0.00	0.00	0.00	$0.00	NAVMEM	NAVISTAR/MIDAMERICA INTL	3,316,685	M	0016870	01	2018	INTL	10/10/17	C1	LT		VMRS CODE: MA 32 01 010 24 99 88
4526	TRAC	16870	357,508	357,508	16700--16899	01/30/21	40	25 Jan 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	001	RPL 1	03	RPLNEW					0.00	0.00	0.00	$513.04			3,395,943	R	1175006	01	2018	INTL	10/10/17	C1	LT		COMPLAINT:RRO DRV TIRE BLOWN
4527	TRAC	16870	357,508	357,508	16700--16899	01/30/21	40	25 Jan 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	999	OTHER	71	SCRAP	01	QTY 1	12	BROKEN	0.00	0.00	0.00	$0.00			3,395,943	R	1175006	01	2018	INTL	10/10/17	C1	LT		DISPOSAL
4528	TRAC	16870	356,218	356,218	16700--16899	01/15/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,376,516	S	0016870	01	2018	INTL	10/10/17	C1	LT		CSA
4529	TRAC	16870	357,508	358,604	16700--16899	02/03/21	40	26 Feb 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	999	OTHER	72	RD SVC					0.00	0.00	0.00	$558.28			3,395,943	R	1175006	01	2018	INTL	10/10/17	C1	LT		SERVICE CALL
4530	TRAC	16870	363,437	363,437	16700--16899	02/28/21	41	26 Feb 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,401,911	S	0016870	01	2018	INTL	10/10/17	C1	LT		CSA
YTD Data

Cell Formulas
Range	Formula
G4518:G4528,G4504:G4512	G4504	=(F4504-AL4504)/(365/12)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T:T	Cell Value	="A"	text	NO

Needing help on this one. I want the far right column to count days since "work code" A was used.
So in this, column AR should say "0" for the days A was used (date located on the 6th column, in this case the date is 3/25/21)
Where it is difficult is that not all units at this point have an "A" work code entered so it also has to take into account the unit number is different and thus shouldn't count but rather be blank.
So where column T has "A" and column B = the same unit number, count days elapsed since the date located on column F
If column B doesn't equal the unit number associated with work code "A" and no "A" work code is present, then return blank.

Any help on this will be a game changer for me. Or if you know of another way to track how many days elapsed for each line since work code "A" was entered taking into account the unit number (column B)


Thank you in advance!!

 

[Oddball2020]

2021 YTD Data.Master.Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
4504	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	PM-ANN	99	NA	01	DUE	3.40	187.00	244.10	$431.10			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		PM ANN
4505	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	14	FRAME	07	5THWHL	001	ASMBLY	01	ADJ	99	NA	21	OUTADJ	0.10	5.50	0.00	$5.50			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		ADJUST FIFTH WHEEL
4506	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	32	CRNKSY	01	BA/CAB	001	BATT	26	RECHRG	04	QTY 4	43	WEAK	0.20	11.00	0.00	$11.00			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		JUMP START AND PUT ON CHARGER
4507	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	32	CRNKSY	01	BA/CAB	001	BATT	03	RPLNEW	04	QTY 4	43	WEAK	1.40	77.00	0.04	$77.04			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		TEST AND REPLACE 4 BATTERIES
4508	TRAC	16869	409,813	409,499	16700--16899	03/25/21	41	27 Mar 21	Term	IN	INDIANAPOLIS	AC	ACCID	04	AERODY	05	SKIRTS	001	FRONT	24	REPAIR	22	LEFT	12	BROKEN	2.20	121.00	137.88	$258.88			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		REPAIR STEP BRACKETS FOR BATTERY COVER
4509	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	DE	DRVERR	15	STEERG	01	WHEEL	001	ST WHL	24	REPAIR	99	NA	12	BROKEN	0.10	5.50	89.16	$94.66			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		R R CITY HORN PAD
4510	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	52	ELACCS	05	CB	003	CBWIRE	19	WIRE	99	NA	46	MISSNG	0.20	11.00	6.59	$17.59			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		R R CB POWER CABLE
4511	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	43	EXHSYS	05	DPF	001	FILTER	55	REGEN	99	NA	01	DUE	1.80	99.00	0.00	$99.00			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		REGEN
4512	TRAC	16869	409,813	409,499	16700--16899	03/26/21	42	27 Mar 21	Term	IN	INDIANAPOLIS	MA	MAINT	01	HVAC	02	AC CAB	261	CONTRL	09	DIANOS	99	NA	00	NOFAIL	0.30	16.50	0.00	$16.50			3,401,508	B	0016869	01	2018	INTL	10/10/17	C1	LT		CHECK MAX AC INOP BLOWS WEAK NOT VERY COLD
4513	TRAC	16869	413,450	414,056	16700--16899	04/18/21	42	28 Apr 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,415,815	S	0016869	01	2018	INTL	10/10/17	C1	LT		CSA
4514	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	001	RPL 1	04	RETRED					0.00	0.00	0.00	$180.00			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		RRI
4515	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	999	OTHER	71	SCRAP	01	QTY 1	12	BROKEN	0.00	0.00	0.00	$10.00			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		SCRAP
4516	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	14	FRAME	09	MUDFLP	003	BRACKT	03	RPLNEW	23	RIGHT	12	BROKEN	0.00	0.00	0.00	$100.50			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		R M F BRKT
4517	TRAC	16869	0	415,165	16700--16899	04/28/21	43	28 Apr 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	14	FRAME	09	MUDFLP	001	MUDFLP	03	RPLNEW	23	RIGHT	49	CUT	0.00	0.00	0.00	$37.49			3,412,107	R	1180432	01	2018	INTL	10/10/17	C1	LT		R M F
4518	TRAC	16870	353,493	353,493	16700--16899	01/07/21	39	25 Jan 21	Vend	RO	ROAD REPAIR	MA	MAINT	00	PWRUNT	00	TRAC	001	UNIT	64	MOTEL	99	NA	88	RECALL	0.00	0.00	0.00	$161.50			3,391,449	R	1207900	01	2018	INTL	10/10/17	C1	LT		COMPLAINT: MOTEL ROOM NIGHT 1 2
4519	TRAC	16870	353,522	353,493	16700--16899	01/06/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		CSA
4520	TRAC	16870	353,522	353,493	16700--16899	01/06/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	PM-ANN	99	NA	01	DUE	3.60	198.00	213.45	$411.45	PTL WM	PTL - West Memphis	3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		PM ANN
4521	TRAC	16870	353,522	353,493	16700--16899	01/07/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	13	BRKTRA	02	DRVAXL	019	BRKCHA	03	RPLNEW	10	R REAR	12	BROKEN	3.00	165.00	62.48	$227.48			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		RR BRAKE CHAMBER BROKEN
4522	TRAC	16870	353,522	353,493	16700--16899	01/06/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	45	ENGINE	10	O DIST	002	FILCAP	03	RPLNEW	99	NA	44	WORN	0.20	11.00	52.83	$63.83			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		REPLACE OIL CAP
4523	TRAC	16870	353,522	353,493	16700--16899	01/07/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	13	BRKTRA	07	LINES	010	SPRING	01	ADJ	99	NA	21	OUTADJ	0.30	16.50	0.00	$16.50			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		AIRLINE NEED ADJ
4524	TRAC	16870	353,522	353,493	16700--16899	01/07/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	MA	MAINT	04	AERODY	05	SKIRTS	003	REAR	24	REPAIR	22	LEFT	19	LOOSE	0.60	33.00	0.00	$33.00			3,387,948	B	1168492	01	2018	INTL	10/10/17	C1	LT		SIDESKIRT LOOSE
4525	TRAC	16870	0	353,493	16700--16899	01/09/21	39	25 Jan 21	Vend	WM	WEST MEMPHIS	MA	MAINT	32	CRNKSY	01	BA/CAB	010	LVD	24	REPAIR	99	NA	88	RECALL	0.00	0.00	0.00	$0.00	NAVMEM	NAVISTAR/MIDAMERICA INTL	3,316,685	M	0016870	01	2018	INTL	10/10/17	C1	LT		VMRS CODE: MA 32 01 010 24 99 88
4526	TRAC	16870	357,508	357,508	16700--16899	01/30/21	40	25 Jan 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	001	RPL 1	03	RPLNEW					0.00	0.00	0.00	$513.04			3,395,943	R	1175006	01	2018	INTL	10/10/17	C1	LT		COMPLAINT:RRO DRV TIRE BLOWN
4527	TRAC	16870	357,508	357,508	16700--16899	01/30/21	40	25 Jan 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	999	OTHER	71	SCRAP	01	QTY 1	12	BROKEN	0.00	0.00	0.00	$0.00			3,395,943	R	1175006	01	2018	INTL	10/10/17	C1	LT		DISPOSAL
4528	TRAC	16870	356,218	356,218	16700--16899	01/15/21	39	25 Jan 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,376,516	S	0016870	01	2018	INTL	10/10/17	C1	LT		CSA
4529	TRAC	16870	357,508	358,604	16700--16899	02/03/21	40	26 Feb 21	Vend	RO	ROAD REPAIR	RH	RD HAZ	96	TIRES	03	DRIVE	999	OTHER	72	RD SVC					0.00	0.00	0.00	$558.28			3,395,943	R	1175006	01	2018	INTL	10/10/17	C1	LT		SERVICE CALL
4530	TRAC	16870	363,437	363,437	16700--16899	02/28/21	41	26 Feb 21	Term	WM	WEST MEMPHIS	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	01	DUE	0.00	0.00	0.00	$0.00			3,401,911	S	0016870	01	2018	INTL	10/10/17	C1	LT		CSA
YTD Data

Cell Formulas
Range	Formula
G4518:G4528,G4504:G4512	G4504	=(F4504-AL4504)/(365/12)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T:T	Cell Value	="A"	text	NO

Needing help on this one. I want the far right column to count days since "work code" A was used.
So in this, column AR should say "0" for the days A was used (date located on the 6th column, in this case the date is 3/25/21)
Where it is difficult is that not all units at this point have an "A" work code entered so it also has to take into account the unit number is different and thus shouldn't count but rather be blank.
So where column T has "A" and column B = the same unit number, count days elapsed since the date located on column F
If column B doesn't equal the unit number associated with work code "A" and no "A" work code is present, then return blank.

Any help on this will be a game changer for me. Or if you know of another way to track how many days elapsed for each line since work code "A" was entered taking into account the unit number (column B)


Thank you in advance!!

I started with this for cell AR4504 =IF(T4504="A", TODAY()-F4504, "")

But because your table doesn't have the header row, it's hard to follow the rest of what you are referring about "unit number". Which column is the unit number?

 

[Noxqss38242]

Column B is the unit number

So count days elapsed since "A" was inputted for 16869
When 16870 starts, it doesn't start "A" until row 4520 so rows 4518 and 4519 should return blank.
IF 16870 never showed work code "A" in column T then it wouldn't count any days lapsed and would remain blank.
Only when another "A" work code in column T with the unit number in column B matching, would a count be done.

 

[Oddball2020]

Column B is the unit number

So count days elapsed since "A" was inputted for 16869
When 16870 starts, it doesn't start "A" until row 4520 so rows 4518 and 4519 should return blank.
IF 16870 never showed work code "A" in column T then it wouldn't count any days lapsed and would remain blank.
Only when another "A" work code in column T with the unit number in column B matching, would a count be done.

Thanks, but this is still fuzzy,

"Only when another "A" work code in column T with the unit number in column B matching, would a count be done."

When column B matches what? Also with how you originally expressed it, "So where column T has "A" and column B = the same unit number," (same unit number as what?)

Is each row's Col B unit number being checked for a match against something on the same row? The row above it?

 

[Noxqss38242]

So I'm trying to figure out how many days elapsed since the next work done on the units after "A" (which in a oil change and inspection)
So when 16869 got it's oil change and inspection, how many days lapsed for that unit on each task afterwards?

Here is a smaller version and what it should look like:

2021 Trac System CPM(charles).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Equip. Type	Equip. Id	Closed Date	Reason Code	Reason Text	System Code	System Text	Assembly Code	Assembly Text	Component Code	Component Text	Work Acc. Code	Column1
2	TRAC	16717	02/23/21	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	0
3	TRAC	16717	02/25/21	AC	ACCID	14	FRAME	02	BUMPER	001	ASMBLY	03	2
4	TRAC	16717	02/22/21	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	-1
5	TRAC	16717	02/25/21	MA	MAINT	22	AXLE	05	R DIFF	001	ASMBLY	01	2
6	TRAC	16717	02/25/21	MA	MAINT	16	SUSPEN	06	DRVAXL	001	SHOCKF	24	2
7	TRAC	16717	02/25/21	MA	MAINT	18	WHLEND	01	STERAX	004	SEAL	09	2
8	TRAC	16717	02/25/21	MA	MAINT	13	BRKTRA	02	DRVAXL	010	SHOE	03	2
9	TRAC	16717	02/25/21	MA	MAINT	13	BRKTRA	11	ABS	005	TRACVL	75	2
10	TRAC	16717	03/31/21	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	36
11	TRAC	16717	04/23/21	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	59
12	TRAC	16717	04/01/21	MA	MAINT	43	EXHSYS	03	EXHPIP	013	CLAMP	32	37
13	TRAC	16717	04/01/21	MA	MAINT	22	AXLE	05	R DIFF	001	ASMBLY	32	37
14	TRAC	16717	04/01/21	MA	MAINT	13	BRKTRA	10	AIRSYS	056	DRYCAR	03	37
15	TRAC	16718	02/22/21	DE	DRVERR	00	PWRUNT	00	TRAC	001	UNIT	10
16	TRAC	16718	02/23/21	MA	MAINT	43	EXHSYS	05	DPF	001	FILTER	55
17	TRAC	16718	02/23/21	MA	MAINT	43	EXHSYS	05	DPF	001	FILTER	64
18	TRAC	16718	02/25/21	MA	MAINT	13	BRKTRA	02	DRVAXL	028	SLACK	75
19	TRAC	16718	03/05/21	MA	MAINT	44	FUELSY	04	DEF	015	QLS	03
20	TRAC	16718	03/05/21	MA	MAINT	43	EXHSYS	05	DPF	001	FILTER	24
21	TRAC	16718	03/19/21	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B
22	TRAC	16719	03/20/21	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	0
23	TRAC	16719	03/20/21	MA	MAINT	13	BRKTRA	11	ABS	069	ECU	09	0
24	TRAC	16719	03/21/21	AC	ACCID	04	AERODY	04	CABEXT	002	RIGHT	03	1
25	TRAC	16719	03/20/21	MA	MAINT	16	SUSPEN	06	DRVAXL	001	SHOCKF	03	0
26	TRAC	16719	03/20/21	MA	MAINT	16	SUSPEN	06	DRVAXL	002	SHOCKR	03	0
27	TRAC	16719	03/20/21	MA	MAINT	14	FRAME	02	BUMPER	013	BGBRKT	05	0
28	TRAC	16719	03/20/21	MA	MAINT	13	BRKTRA	02	DRVAXL	019	BRKCHA	03	0
29	TRAC	16719	03/29/21	MA	MAINT	13	BRKTRA	07	LINES	003	FITTNG	03	9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L1:L29	Cell Value	="a"	text	NO

So I know how to get the formula for elapsed days but I don't know how to incorporate the fact it also needs to be unit specific and work code specific.
When saying B = same unit number, it has to match the unit number the work was done on when "A" was used.
So "A" shows up on 16717 in this case and the unit matches B for rows 3 to 14 and so should be calculating how many days elapsed
A different unit number comes into play on row 15 so it should return a blank if it also doesn't contain "A" in column L
If it contained "A" when unit number changes, then all that would happen is that the elapsed time would reset since it is a new unit number.

Hope that makes for a better read.

 

[Oaktree]

Maybe something like this (after adjusting the ranges from rows 2:10 to be whatever you need them to be)?

=IF(MAXIFS($G$2:$G$10,$T$2:$T$10,"A",$B$2:$B$10,B2,$G$2:$G$10,"<"&G2)=0,"",G2-MAXIFS($G$2:$G$10,$T$2:$T$10,"A",$B$2:$B$10,B2,$G$2:$G$10,"<"&G2))

Basically using MAXIFS to find the largest date that was before the current row's date where the same line (column B) had an "A" in column T.

That should work, but it's going to be slow to recalculate. If you need something faster, you could sort your data by line then by descending date first and use a different approach, where calculating the date of the last "A" is easier.

 

[Noxqss38242]

Maybe something like this (after adjusting the ranges from rows 2:10 to be whatever you need them to be)?

=IF(MAXIFS($G$2:$G$10,$T$2:$T$10,"A",$B$2:$B$10,B2,$G$2:$G$10,"<"&G2)=0,"",G2-MAXIFS($G$2:$G$10,$T$2:$T$10,"A",$B$2:$B$10,B2,$G$2:$G$10,"<"&G2))

Basically using MAXIFS to find the largest date that was before the current row's date where the same line (column B) had an "A" in column T.

That should work, but it's going to be slow to recalculate. If you need something faster, you could sort your data by line then by descending date first and use a different approach, where calculating the date of the last "A" is easier.

I'm not gonna lie, that got my head twisted. :P
I tried rearranging/adjusting but it's not working for me...probably me....trying but still haven't figured it out.

 

[Fluff]

Unfortunately that formula won't work for you as you don't have the maxifs function.
Another option based on the data in post#5

=IF(L2="A",0,IFNA(C2-LOOKUP(2,1/(L$2:L2="A")/(B$2:B2=B2),C$2:C2),""))

 

[Noxqss38242]

Worked brilliantly! Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.