Extract Letters or Numbers from one field and look them up in another

[MacIndy]

I'm looking for a formula that can do the following:
Extract Revision or Change #'s from Column D and search for those numbers or letters at the very end of the text string in Column B. Since the text in Column D has been entered inconsistently I'm guessing there needs to be a way to remove the text strings "Rev" and "CHG" and any punctuation found. The remaining characters should hopefully be the Rev or Change Letter or Number.

Then, I would like to search for that letter or number as the last 1 or 2 characters in the text string in Column B to see if it has been added to the Description Text String in Column B. For a variety of reasons, we need the Revision / Change letter / number to also be at the tail end of the Description.

The idea would be to display something like "Not Found" when the Rev of Chg #/letter is not found at the end of the description.

A bonus ability might be to display something like "NA" if the part # in the same row in Column A contains a "_" anywhere in the text string or if the text string TLG exists in the same row in Column A


See below for some sample data:

Compare COBlanketLineDescription_to_Item.xlsx
	A	B	C	D
1	Item	Description	Unit Weight	Revision
2	461	..CORE PIN	0	CHG. 0
3	546	RESERVE	0
4	2662	..WASHER FAN CLAMP	0	REV. C
5	3197	DUMMY D# FOR COPY ITEMS	0
6	3204	DUMMY D# FOR COPY ITEMS	0
7	3725	DUMMY D# FOR COPY ITEMS	0
8	3726	DUMMY D# FOR COPY ITEMS	0
9	3785	DUMMY D# FOR COPY ITEMS	0
10	4038	..DOWEL PIN	0
11	4118	..WASHER CORK 714725	0	REV. A
12	4162	RESERVE	0
13	4164	..SCREW KNOB ADJ. 5158351 REV-C	1	REV. C
14	4420	..RETAINER (SEAL) 496435	0	NO REV.
15	5102	..GASKET FAN (496445)	0.05
16	5127	..SPACER 5168564	0	REV. B
17	5501	..GASKET (496450)	0
18	6701	..RETAINER (496464)	0.05	REV. 0
19	6702	..WASHER (496465)	0	REV. A
20	6703	..CORK WASHER (496466)	0.002
21	8560	..GASKET FAN L716T	0	CHG. H
22	8561	..IMPELLER (OIL)	0	REV. G
23	10173	..WASHER	0.446	REV. A
24	16193	..WASHER	0	CHG. C
25	19564	..BEARING SPACER	0	REV. C
26	32984	RESERVE	0
27	41884	..w&s part number 115520	0
28	65151	..SF-SLEEVE REV 1	0.7	REV. 1
29	67530	..SPINDLE 8351268 REV 1	9	REV. 1
30	67531	..SPINDLE 8351269 REV 1	12	REV. 1
31	90851	..RING	0	REV. B
32	90915	..F CONVERT 65087 INTO 90915	100
33	91323	.BL CARRIER-PLANET - BLANK	184
34	91479	..SF GEAR	0	CHG. 2
35	101576	..HUB	0
36	101577	..SPINDLE (496511)	2.97	REV. Q
37	103280	..PULLEY CHAIN	0	REV. A
38	104297	..SCREW ADJUSTABLE 5125137	0.38
39	104537	..BEARING TRW_MRC5306_CF	0
40	104726	..WASHER CHG E	0	CHG. E
41	105566	..HUB	0	C
42	105567	..BRACKET 5158350	0	E
43	2V7344_CASTING	.C DUCTILE IRON 1E0356	55	CHG. 09
44	2V9352	..CASE ASSEMBLY	10	1
45	2W0572	..ELBOW CHG 00	10	0
46	2W0572_CASTING	.C 1E0020 GREY IRON CASTING	11.7
47	2W0572_TLG	..T FIXTURES M.C. HOLDING	0
48	2W0572_TOOLING	.TLG RE-RIGG EXISTING TOOLING	0
49	2W3261	..MANIFOLD-EXHAUST CHG 5	50.2	5
50	2W3261_CASTING	.C 1E0628 DCTL IRON CASTING	55	CHG. 05
51	2W3261_TLG	..TOOLING/FIXTURES	0
52	2W3261_TOOLING	.TLG RE-RIGG PATTERN	0	5
53	2W5096	..BRACKET CHG 8	19.35	8
54	2W5096_CASTING	.C DUCTILE IRON SAE D4512	20.82	CHG. 08
55	2W5244	..SHAFT	0	2
56	2W7087	RESERVE	0
57	2W8274	RESERVE	0
58	2W8494_CASTING	.C GRAY IRON 1E0018 SAE G3000	8.5
59	2W9172	..ADAPTER-AIR CLEANER CHG 00	1.69	0
60	2W9172_CASTING	.C 1E0485 SAE 326 ALUM CASTING	1.8	0
61	2W9172_TLG	..TOOLING/FIXTURE	0
62	2Y4678	..TUBE	0	2
63	2Y8743	RESERVE	0
64	3065621_BLAST	.BLST BLAST TO REMOVE SCALE	7.86
65	3065621_CH	.CH CARBORIZE AND HARDEN	5
66	3065621_OM	.OM ISOTROPIC FINISH PER B/P	0
67	3065622_BLAST	.BLST BLAST TO REMOVE RUST	9.66
68	3065622_CH	.CH CARBORIZE AND HARDEN	7
69	3065622_OM	.OM ISOTROPIC FINISH PER B/P	6.5
70	3065623_BLAST	.BLST BLAST TO REMOVE SCALE	11.6
71	3065623_CH	.CH CARBORIZE AND HARDEN	11.6
72	3065623_OM	.OM ISOTROPIC FINISH PER B/P	9
73	3065624_BLAST	.BLST BLAST TO REMOVE SCALE	15.36
74	3065624_CH	.CH CARBORIZE AND HARDEN	12
75	3065624_OM	.OM ISOTROPIC FINISH PER B/P	0
76	3100811_TLG	.TLG PPAP COSTS	0
77	3102129_CASTING	.C 1E0018 GREY IRON CASTING	38	3
78	3102129_PNT	.PNT 1E2731A CAT YELLOW	38
79	3102129_TLG	..TLG FIXTURE/TOOLING RE-RIGG PATTERN	0
80	3102129_TOOLING	.TLG RE-RIGG PATTERN	0	3
81	3106159_BLAST	.BLST BLAST TO REMOVE SCALE	8.8
82	3106159_CH	.CH CARBORIZE AND HARDEN	8.275
83	3106159_FORGING	.F 1E1120 STEEL FORGING W/HNM	11.2
84	3106159_GRIND	.GRD GRIND (4) JOURNALS	0
85	3106159_HNM	.HNM HIGH TEMP NORMALIZE	11.2
86	3106159_MAG	.MAG MANAFLUX FOR CRACKS	8
87	3106159_OM	.OM ISOTROPIC FINISH	8.8
88	3123599_BLANK	.B SAE 1026 BLANK RING TO DWG	1,240.00
89	3123599CC_BLANK	.B SAE 1026 CON-CAST BLANK RING TO DWG	1,850.00
90	3161974_BAL	.B BALANCE PER B/P	0	CHG. 01
91	3161974_CASTING	.C 1E1122 DCTL IRON CASTING	0	CHG. 01
92	3161974_RING	..R RE-WORK RING	0
93	3161974_TET	.TET CUT SPLINE PER B/P	0
94	3161974_TLG	..TLG SPLINE/FIXTURE/BAL ARBOR	0
95	3161974_TOOLING	.TLG SPLINE CUTTERS/FIXTURES	0
96	3178373_CASTING	.C 1E0018 GREY IRON CASTING	33.6
97	3178373_TLG	..T NEW PATTERN EQUIPMENT	0
98	3178373_TOOLING	.TLG NEW PATTERN EQUIPMENT	0
99	3204410_TLG	..TLG MACHINE-WELD FIXTURES	0
100	3204410_WLD	.WLD WELD PER PRINT	0
101	3204412_BLANK	.B 8" SQUARE SAE 4140 1E0509	0
102	3204413_WLD	.WLD WELD PER PRINT	0
103	3204415_BLANK	.B 1E0170 6 1/2" X 3 3/4" X 3 3/4" OAL	0
104	3204A	DUMMY D# FOR COPY ITEMS	0
105	3204B	DUMMY D# FOR COPY ITEMS	0
106	3257477_CASTING	.C 1E0596A DCTL IRON CASTING	97.4
107	3257477_IH	.IH FLAME HDN PER 1E0399A	0
108	3257477_PNT	.PNT CAT YELLOW PAINT	0
109	3303501_CASTING	.C 1E0018 GREY IRON CASTING	145	1
110	3303501_PNT	.PNT 1E2731A CAT YELLOW	145	1
111	3303501_TLG	..TLG FIXTURE/TOOLING RE-RIGG PATTERN	0
112	3303501_TOOLING	.TLG RE-RIGG PATTERN	0	1
MRExcel

 

[kweaver]

Is "CHG 00" the same as "CHG 0" ?

 

[MacIndy]

Is "CHG 00" the same as "CHG 0" ?

Yes, unfortunately. When we report what we have shipped to our customer we report all change codes through EDI as 2 digit codes. So, technically, all "0" should be"00" and "1" should be "01", but for now I would be happy just getting a comparison between the two fields.

 

[kweaver]

Is this even close?

Book52
	A	B	C	D	E	F
1	Item	Description	Unit Weight	Revision
2	461	..CORE PIN	0	CHG. 0
3	546	RESERVE	0
4	2662	..WASHER FAN CLAMP	0	REV. C
5	3197	DUMMY D# FOR COPY ITEMS	0
6	3204	DUMMY D# FOR COPY ITEMS	0
7	3725	DUMMY D# FOR COPY ITEMS	0
8	3726	DUMMY D# FOR COPY ITEMS	0
9	3785	DUMMY D# FOR COPY ITEMS	0
10	4038	..DOWEL PIN	0
11	4118	..WASHER CORK 714725	0	REV. A
12	4162	RESERVE	0
13	4164	..SCREW KNOB ADJ. 5158351 REV-C	1	REV. C		REV. C
14	4420	..RETAINER (SEAL) 496435	0	NO REV.
15	5102	..GASKET FAN (496445)	0.05
16	5127	..SPACER 5168564	0	REV. B
17	5501	..GASKET (496450)	0
18	6701	..RETAINER (496464)	0.05	REV. 0
19	6702	..WASHER (496465)	0	REV. A
20	6703	..CORK WASHER (496466)	0.002
21	8560	..GASKET FAN L716T	0	CHG. H
22	8561	..IMPELLER (OIL)	0	REV. G
23	10173	..WASHER	0.446	REV. A
24	16193	..WASHER	0	CHG. C
25	19564	..BEARING SPACER	0	REV. C
26	32984	RESERVE	0
27	41884	..w&s part number 115520	0
28	65151	..SF-SLEEVE REV 1	0.7	REV. 1		REV. 1
29	67530	..SPINDLE 8351268 REV 1	9	REV. 1		REV. 1
30	67531	..SPINDLE 8351269 REV 1	12	REV. 1		REV. 1
31	90851	..RING	0	REV. B
32	90915	..F CONVERT 65087 INTO 90915	100
33	91323	.BL CARRIER-PLANET - BLANK	184
34	91479	..SF GEAR	0	CHG. 2
35	101576	..HUB	0
36	101577	..SPINDLE (496511)	2.97	REV. Q
37	103280	..PULLEY CHAIN	0	REV. A
38	104297	..SCREW ADJUSTABLE 5125137	0.38
39	104537	..BEARING TRW_MRC5306_CF	0		_ found
40	104726	..WASHER CHG E	0	CHG. E		CHG. E
41	105566	..HUB	0	C
42	105567	..BRACKET 5158350	0	E
43	2V7344_CASTING	.C DUCTILE IRON 1E0356	55	CHG. 09
44	2V9352	..CASE ASSEMBLY	10	1
45	2W0572	..ELBOW CHG 00	10	0		0
46	2W0572_CASTING	.C 1E0020 GREY IRON CASTING	11.7
47	2W0572_TLG	..T FIXTURES M.C. HOLDING	0
48	2W0572_TOOLING	.TLG RE-RIGG EXISTING TOOLING	0		TLG found
49	2W3261	..MANIFOLD-EXHAUST CHG 5	50.2	5		5
50	2W3261_CASTING	.C 1E0628 DCTL IRON CASTING	55	CHG. 05
51	2W3261_TLG	..TOOLING/FIXTURES	0
52	2W3261_TOOLING	.TLG RE-RIGG PATTERN	0	5	TLG found
53	2W5096	..BRACKET CHG 8	19.35	8		8
Sheet1

Cell Formulas
Range	Formula
E2:E53	E2	=IF(ISNUMBER(FIND("_",B2)),"_ found ",IF(ISNUMBER(FIND("TLG",B2)),"TLG found",""))
F2:F53	F2	=IF(ISBLANK(D2),"",IF(ISERROR(FIND(SUBSTITUTE(D2,".",""),RIGHT(SUBSTITUTE(B2,"-"," "),LEN(D2)))),"",D2))

 

[MacIndy]

Thank you for working on this. Its very close, I think. For the first formula I updated the column from B to A.

The second formula that compares D to B works. I would probably just add putting "N/A" in column E if Column D is blank.

Now that I have seen the results of data filtered with your formula I can see that I may need another side to the second formula that tells me when a CHG or REV is found in Column B but isn't in Column D