Exclude data based on cell value

[Noxqss38242]

Work Between PMs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Equip. Type	Equip. Id	Terminal Code	Reason Code	Reason Text	System Code	System Text	Assembly Code	Assembly Text	Component Code	Component Text	Work Acc. Code	Work Acc. Text	Position Code	Position Text	Failure Code	Failure Text	Task Hours	Task Labor	Task Parts	Task Total	Warranty Filed	Orig. Key
2	P	16906	WM	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	PM-ANN	99	NA	01	DUE	3.3	181.5	217.78	399.28		3516788
3	P	16906	WM	MA	MAINT	13	BRKTRA	09	ACOMPR	001	ASMBLY	09	DIANOS	99	NA	00	NOFAIL	0.9	49.5	0	49.5		3516788
4	P	16906	WM	MA	MAINT	13	BRKTRA	07	LINES	001	RUBBER	03	RPLNEW	13	LFRONT	49	CUT	0.5	27.5	12.23	39.73		3516788
5	P	16907	RO	MA	MAINT	01	HVAC	01	ACHOOD	002	COMPRS	09	DIANOS	99	NA	12	BROKEN	0	0	0	0		3514197
6	P	16907	RO	RH	RD HAZ	96	TIRES	01	STEER	102	RIGHT	03	RPLNEW					0	0	0	434.36		3522825
7	P	16907	RO	RH	RD HAZ	96	TIRES	01	STEER	999	OTHER	71	SCRAP	01	QTY 1	18	LEAK	0	0	0	9.95		3522825
8	P	16908	LR	MA	MAINT	45	ENGINE	12	COOLER	001	ENGOIL	03	RPLNEW	99	NA	18	LEAK	0	0	0	519.06		3516886
9	P	16909	RO	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	PM-ANN	99	NA	01	DUE	0	0	0	495.61		3510347
10	P	16909	RO	MA	MAINT	32	CRNKSY	01	BA/CAB	001	BATT	03	RPLNEW	02	QTY 2	43	WEAK	0	0	0	495.62		3510347
11	P	16909	RO	MA	MAINT	01	HVAC	01	ACHOOD	002	COMPRS	03	RPLNEW	99	NA	12	BROKEN	0	0	0	315.29		3523108
12	P	16909	RO	MA	MAINT	42	COOLSY	03	FANASY	010	BELT	03	RPLNEW	99	NA	12	BROKEN	0	0	0	20.77		3523108
13	P	16909	RO	MA	MAINT	01	HVAC	01	ACHOOD	048	BELT	03	RPLNEW	99	NA	12	BROKEN	0	0	0	36.69		3523108
14	P	16909	RO	MA	MAINT	13	BRKTRA	09	ACOMPR	038	UNLOAD	03	RPLNEW	99	NA	12	BROKEN	0	0	0	25.26		3523108
15	P	16909	RO	MA	MAINT	42	COOLSY	03	FANASY	002	IDLER	03	RPLNEW	99	NA	12	BROKEN	0	0	0	0		3523108
16	P	16909	RO	MA	MAINT	01	HVAC	01	ACHOOD	285	LINE	03	RPLNEW	99	NA	12	BROKEN	0	0	0	21.25		3523108
17	P	16912	RO	AC	ACCID	02	CAB	10	MIRROR	018	R DOOR	03	RPLNEW	23	RIGHT	12	BROKEN	0	0	0	199.5		3520406
18	P	16912	MU	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	A	PM-ANN	99	NA	01	DUE	3	165	206.6	371.6		3515772
19	P	16912	MU	AC	ACCID	14	FRAME	02	BUMPER	001	ASMBLY	03	RPLNEW	99	NA	98	ACCID	0.9	49.5	723.59	773.09		3515772
20	P	16912	MU	AC	ACCID	02	CAB	10	MIRROR	026	HOOD	03	RPLNEW	22	LEFT	98	ACCID	0.7	38.5	135.36	173.86		3515772
21	P	16912	MU	MA	MAINT	18	WHLEND	01	STERAX	026	HUBCAP	24	REPAIR	23	RIGHT	08	OVRFUL	0.7	38.5	0	38.5		3515772
22	P	16912	MU	MA	MAINT	02	CAB	35	WSWIPE	015	BLADE	03	RPLNEW	02	QTY 2	44	WORN	0.3	16.5	8	24.5		3515772
23	P	16912	MU	AC	ACCID	04	AERODY	04	CABEXT	004	RUBBER	03	RPLNEW	19	LORITE	46	MISSNG	0.4	22	146.98	168.98		3515772
24	P	16912	MU	MA	MAINT	15	STEERG	05	PWRSTE	002	RESERV	24	REPAIR	99	NA	18	LEAK	0.8	44	0	44		3515772
25	P	16912	MU	AC	ACCID	04	AERODY	04	CABEXT	001	LEFT	03	RPLNEW	22	LEFT	12	BROKEN	0.6	33	195.29	228.29		3515772
26	P	16912	MU	PM	PM	00	PWRUNT	00	TRAC	001	UNIT	B	B INSP	99	NA	27	F-INSP	0	0	0	0		3515772
27	P	16912	MU	MA	MAINT	14	FRAME	02	BUMPER	013	BGBRKT	03	RPLNEW	99	NA	19	LOOSE	0.8	44	485.75	529.75		3515772
28	P	16912	MU	MA	MAINT	02	CAB	11	INSIDE	001	BUNK	24	REPAIR	99	NA	19	LOOSE	0.5	27.5	0	27.5		3515772
29	P	16912	MU	MA	MAINT	96	TIRES	03	DRIVE	004	QTY 4	04	RETRED					0.8	44	765	809		3515772
30	P	16912	MU	MA	MAINT	96	TIRES	03	DRIVE	999	OTHER	09	DIANOS	04	QTY 4	44	WORN	0.4	22	0	22		3515772
31	P	16912	MU	MA	MAINT	96	TIRES	01	STEER	102	RIGHT	03	RPLNEW					0.4	22	416.73	438.73		3515772
32	P	16912	MU	MA	MAINT	13	BRKTRA	02	DRVAXL	050	PAD	03	RPLNEW	13	LFRONT	44	WORN	1.2	66	73.28	139.28		3515772
33	P	16912	MU	MA	MAINT	13	BRKTRA	02	DRVAXL	050	PAD	03	RPLNEW	10	R REAR	44	WORN	0.8	44	73.28	117.28		3515772
34	P	16912	MU	MA	MAINT	32	CRNKSY	01	BA/CAB	001	BATT	24	REPAIR	04	QTY 4	24	EL CRS	0.9	49.5	0	49.5		3515772
35	P	16912	MU	MA	MAINT	96	TIRES	03	DRIVE	100	ROTATE	09	DIANOS	99	NA	21	OUTADJ	0.4	22	0	22		3515772
36	P	16913	MU	PM	PM	45	ENGINE	01	ASSMBY	001	COMPLT	V	OVERHD	99	NA	01	DUE	0	0	0	491		3467352
Sheet2

So for this data, "Work Acc. Code" is in column L and the identifier for the work order is "Orig. Key" in column W
If L2 is "A" or "C", then whatever the identifier code is (which is 3516788 in this case, ignore any work done with that value.
Given that rows, 1 - 3 should be ignored or given a value I can track like "0" or "1"...doesn't matter, looking for a way to exclude them...
Rows 4 - 8 should then obviously be included so it can be given a different value in the formula because it doesn't match what is to be ignored...
Still, an "A" code shows up in row 9, so its identifier of "3510347" should also be ignored...meaning row 10 should be excluded and row 11 be included since the identifier doesn't match though the same unit number.

Essentially...hopefully...come up with a code that says if L2 = "A" or "C", then the Value of W anywhere else in the column = "0" BUT need the code to get all "A" or "C" identifiers
So if "A" or "C" shows up, grab the identifier in column "W" that corresponds and exclude all matches of the identifier.

Lord, I hope I explained that well enough... :P

Any solution?

 

[Kerryx]

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Equip. Type	Equip. Id	Terminal Code	Reason Code	Reason Text	System Code	System Text	Assembly Code	Assembly Text	Component Code	Component Text	Work Acc. Code	Work Acc. Text	Position Code	Position Text	Failure Code	Failure Text	Task Hours	Task Labor	Task Parts	Task Total	Warranty Filed	Orig. Key	ID2
2	P	16906	WM	PM	PM	0	PWRUNT	0	TRAC	1	UNIT	A	PM-ANN	99	NA	1	DUE	3.3	181.5	217.78	399.28		3516788	3516788
3	P	16906	WM	MA	MAINT	13	BRKTRA	9	ACOMPR	1	ASMBLY	9	DIANOS	99	NA	0	NOFAIL	0.9	49.5	0	49.5		3516788	3516788
4	P	16906	WM	MA	MAINT	13	BRKTRA	7	LINES	1	RUBBER	3	RPLNEW	13	LFRONT	49	CUT	0.5	27.5	12.23	39.73		3516788	3516788
5	P	16907	RO	MA	MAINT	1	HVAC	1	ACHOOD	2	COMPRS	9	DIANOS	99	NA	12	BROKEN	0	0	0	0		3514197	0
6	P	16907	RO	RH	RD HAZ	96	TIRES	1	STEER	102	RIGHT	3	RPLNEW					0	0	0	434.36		3522825	0
7	P	16907	RO	RH	RD HAZ	96	TIRES	1	STEER	999	OTHER	71	SCRAP	1	QTY 1	18	LEAK	0	0	0	9.95		3522825	0
8	P	16908	LR	MA	MAINT	45	ENGINE	12	COOLER	1	ENGOIL	3	RPLNEW	99	NA	18	LEAK	0	0	0	519.06		3516886	0
9	P	16909	RO	PM	PM	0	PWRUNT	0	TRAC	1	UNIT	A	PM-ANN	99	NA	1	DUE	0	0	0	495.61		3510347	3510347
10	P	16909	RO	MA	MAINT	32	CRNKSY	1	BA/CAB	1	BATT	3	RPLNEW	2	QTY 2	43	WEAK	0	0	0	495.62		3510347	3510347
11	P	16909	RO	MA	MAINT	1	HVAC	1	ACHOOD	2	COMPRS	3	RPLNEW	99	NA	12	BROKEN	0	0	0	315.29		3523108	0
12	P	16909	RO	MA	MAINT	42	COOLSY	3	FANASY	10	BELT	3	RPLNEW	99	NA	12	BROKEN	0	0	0	20.77		3523108	0
13	P	16909	RO	MA	MAINT	1	HVAC	1	ACHOOD	48	BELT	3	RPLNEW	99	NA	12	BROKEN	0	0	0	36.69		3523108	0
14	P	16909	RO	MA	MAINT	13	BRKTRA	9	ACOMPR	38	UNLOAD	3	RPLNEW	99	NA	12	BROKEN	0	0	0	25.26		3523108	0
15	P	16909	RO	MA	MAINT	42	COOLSY	3	FANASY	2	IDLER	3	RPLNEW	99	NA	12	BROKEN	0	0	0	0		3523108	0
16	P	16909	RO	MA	MAINT	1	HVAC	1	ACHOOD	285	LINE	3	RPLNEW	99	NA	12	BROKEN	0	0	0	21.25		3523108	0
17	P	16912	RO	AC	ACCID	2	CAB	10	MIRROR	18	R DOOR	3	RPLNEW	23	RIGHT	12	BROKEN	0	0	0	199.5		3520406	0
18	P	16912	MU	PM	PM	0	PWRUNT	0	TRAC	1	UNIT	A	PM-ANN	99	NA	1	DUE	3	165	206.6	371.6		3515772	3515772
19	P	16912	MU	AC	ACCID	14	FRAME	2	BUMPER	1	ASMBLY	3	RPLNEW	99	NA	98	ACCID	0.9	49.5	723.59	773.09		3515772	3515772
20	P	16912	MU	AC	ACCID	2	CAB	10	MIRROR	26	HOOD	3	RPLNEW	22	LEFT	98	ACCID	0.7	38.5	135.36	173.86		3515772	3515772
21	P	16912	MU	MA	MAINT	18	WHLEND	1	STERAX	26	HUBCAP	24	REPAIR	23	RIGHT	8	OVRFUL	0.7	38.5	0	38.5		3515772	3515772
22	P	16912	MU	MA	MAINT	2	CAB	35	WSWIPE	15	BLADE	3	RPLNEW	2	QTY 2	44	WORN	0.3	16.5	8	24.5		3515772	3515772
23	P	16912	MU	AC	ACCID	4	AERODY	4	CABEXT	4	RUBBER	3	RPLNEW	19	LORITE	46	MISSNG	0.4	22	146.98	168.98		3515772	3515772
24	P	16912	MU	MA	MAINT	15	STEERG	5	PWRSTE	2	RESERV	24	REPAIR	99	NA	18	LEAK	0.8	44	0	44		3515772	3515772
25	P	16912	MU	AC	ACCID	4	AERODY	4	CABEXT	1	LEFT	3	RPLNEW	22	LEFT	12	BROKEN	0.6	33	195.29	228.29		3515772	3515772
26	P	16912	MU	PM	PM	0	PWRUNT	0	TRAC	1	UNIT	B	B INSP	99	NA	27	F-INSP	0	0	0	0		3515772	3515772
27	P	16912	MU	MA	MAINT	14	FRAME	2	BUMPER	13	BGBRKT	3	RPLNEW	99	NA	19	LOOSE	0.8	44	485.75	529.75		3515772	3515772
28	P	16912	MU	MA	MAINT	2	CAB	11	INSIDE	1	BUNK	24	REPAIR	99	NA	19	LOOSE	0.5	27.5	0	27.5		3515772	3515772
29	P	16912	MU	MA	MAINT	96	TIRES	3	DRIVE	4	QTY 4	4	RETRED					0.8	44	765	809		3515772	3515772
30	P	16912	MU	MA	MAINT	96	TIRES	3	DRIVE	999	OTHER	9	DIANOS	4	QTY 4	44	WORN	0.4	22	0	22		3515772	3515772
31	P	16912	MU	MA	MAINT	96	TIRES	1	STEER	102	RIGHT	3	RPLNEW					0.4	22	416.73	438.73		3515772	3515772
32	P	16912	MU	MA	MAINT	13	BRKTRA	2	DRVAXL	50	PAD	3	RPLNEW	13	LFRONT	44	WORN	1.2	66	73.28	139.28		3515772	3515772
33	P	16912	MU	MA	MAINT	13	BRKTRA	2	DRVAXL	50	PAD	3	RPLNEW	10	R REAR	44	WORN	0.8	44	73.28	117.28		3515772	3515772
34	P	16912	MU	MA	MAINT	32	CRNKSY	1	BA/CAB	1	BATT	24	REPAIR	4	QTY 4	24	EL CRS	0.9	49.5	0	49.5		3515772	3515772
35	P	16912	MU	MA	MAINT	96	TIRES	3	DRIVE	100	ROTATE	9	DIANOS	99	NA	21	OUTADJ	0.4	22	0	22		3515772	3515772
36	P	16913	MU	PM	PM	45	ENGINE	1	ASSMBY	1	COMPLT	V	OVERHD	99	NA	1	DUE	0	0	0	491		3467352	0
Sheet1

Cell Formulas
Range	Formula
X2	X2	=IF(OR(L2="A",L2="C"),W2,0)
X3:X36	X3	=IF(OR(L3="A",L3="C",W3=X2),W3,0)

 

[Noxqss38242]

I believe I can make that formula work for what I need. Will just have to make sure to sort by W, then by L.

Thank you!