Formula help

[tispivey]

Hello all,

I have a couple issues I need help with. I am attempting to populate data into Column D and G in the attached spreadsheet.

The formulas I have, pulls the code from A, compares it to H, and returns what is in I into C. When I attempt to pull the rows that have multiple codes, and populate into D, it gives me an error because there are spaces at the end of the code in A. If I manually remove them, it works, but when I attempt to add TRIM, it doesn't do anything, so I get an error.

The same thing is happening when I attempt to do the same for column G for the ones with three codes. There are a couple in row B that have 4 different codes that I am going to work on that once I get this under control.

Any help you all can provide would be greatly appreciated.

I am hoping this sheet has enough data for you guys, because there are almost 300 lines, and the addon wouldn't let me add them all.

The picture I attached is what I see, as when I uploaded it, it doesnt look like you got the same format.

Test1234.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	TC REASON	COMPL CDE	REASON DESCPT	REASON DESCPT 2	FIX CD	FIX CD 2	FIX CD 3	REASON KEY		Charge Back Code	Charge Back Description
2	WN	151	WIFI NOT WKG		RE-AUTH WIFI			TB	BUS TBL CALL	165	-48 V FAILURE
3	WN	151	WIFI NOT WKG		RE-AUTH WIFI			1T	1 TV	906	A/B SWITCH
4	CVIN	419	CNV/DCT PROBLEM		REPL TIVO MINI			EO	ECHO	904	A/O INSTALLED
5	CVIN	419	CNV/DCT PROBLEM		REPL TIVO MINI			SV	SAVE PROGRAM	905	A/O RECONNECTED
6	DP	453	DVR PROJECT		RPL INT PASSIVE			SD	SATELLITE DISH	312	ADD HSE AMP
7	DP	453	DVR PROJECT		RPL INT PASSIVE			PV	PPV NOT WORKING	434	ADD/REM TRAP
8	MA	354450439	MDM NOT ACQUIRE		CHNG ROUTER LOC	UNAUTH HOOK UP$		MS	MODEM SLW SPEED	404	ADJUST TV SET
9	EN	435	EMTA NO BLK SNC		RPL EXT CORD			OM	ONE MOMENT PLS	405	ADJUST VCR
10	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			NP	NO PICTURE	311	ANIMAL CHEW
11	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			HC	HSD NEW CONNECT	503	AUDIT ERROR
12	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			TO	TIVO	114	CABLE SUCK OUT
13	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			TH	HSD TROUBLE CALL	12	CANCEL @ PRECLL
14	MA	370	MDM NOT ACQUIRE		REPLACE EMTA SL			PC	PHONE NEW CONNECT	5	CANCEL AT DOOR
15	OM	429	ONE MOMENT PLS		TIVO SVC ACCT			QI	MUST-DO TC HSD DTA	34	CANCEL AT DOOR$
16	MA	432	MDM NOT ACQUIRE		RPL EXT PASSIVE			7T	7 TVS	11	CANCEL BY OFFCE
17	AO	050	ADDITIONAL		REF TO INSTALL			DC	DAMAGED CABLE	7	CANCEL BY OFFIC
18	TO	428	TIVO		REPR TIVO BDBND			VO	VOD NOT WORKING	8	CANCEL@PRECALL
19	MA	051439	MDM NOT ACQUIRE		REF TO MAINT			DT	DTA PROBLEM	10	CANCELLD BY SUB
20	IM	151	INTR MOD NO ACQ		RE-AUTH WIFI			EI	EMTA INT BL SNC	6	CANCELLD BY SUB
21	IM	151	INTR MOD NO ACQ		RE-AUTH WIFI			BP	BUS PHONE	354	CHNG ROUTER LOC
22	MA	439	MDM NOT ACQUIRE		RPL GROUND BLCK			3T	3 TVS	166	CMTS
23	MA	431451	MDM NOT ACQUIRE		REPAIR EXT DRP	NO PROBLEM FND		GH	GHOSTING	134	COMM POWER
24	MA	151	MDM NOT ACQUIRE		RE-AUTH WIFI			H5	HM CAMERA	102	COMML PWR FAIL
25	MA	438	MDM NOT ACQUIRE		DISCO IN ERROR			SI	SELF INSTALLATION	350	CONF WLESS CST$
26	MA	438	MDM NOT ACQUIRE		DISCO IN ERROR			8T	8 TVS	117	CRACKED SHIELD
27	MA	151	MDM NOT ACQUIRE		RE-AUTH WIFI			DD	DWN OR LOW DROP	38	CUST EDUCATION$
28	MA	430	MDM NOT ACQUIRE		REPLACE EXT DRP			VP	VCR PROBLEM	36	CUST INSTAL EQ$
29	MA	430	MDM NOT ACQUIRE		REPLACE EXT DRP			RC	REMOTE PROBLEM	407	CUST INSTALD EQ
30	MA	051	MDM NOT ACQUIRE		REF TO MAINT			IP	INTERMITTENT PRB	218	CUST PHONE PROB
31	MA	051	MDM NOT ACQUIRE		REF TO MAINT			SG	SIGNAL LEAK - HOME	400	CUSTOMER EDUCAT
32	MA	D30	MDM NOT ACQUIRE					WN	WIFI NOT WKG	146	CUT/DAMAGED FIB
33	SC	051	SNGL CHANL OUT		REF TO MAINT			LW	LOW CHAN PROB	120	CUT/DMGD FIBER
34	DP	050	DVR PROJECT		REF TO INSTALL			CM	CUSTOMER IMPACT MAINT	D30	D3 SWAP
35	DP	050	DVR PROJECT		REF TO INSTALL			AU	FIELD AUDIT	406	DEFECTVE A/B SW
36	MA	051453	MDM NOT ACQUIRE		REF TO MAINT	NO ADULT		CV	CNV/DCT PROBLEM	306	DIRECT P/U DROP
37	IM	434432	INTR MOD NO ACQ		ADD/REM TRAP	OK ON TEST SET		CI	CLI	402	DIRECTP/USUB EQ
38	AO	437	ADDITIONAL		RPL EXT F CONN			BI	BUS INTERNET	910	DISCO AT TAP
39	OM	417	ONE MOMENT PLS		REPL TIVO Q			BS	BUS SITE SURVEY	500	DISCO IN ERROR
40	AO	437	ADDITIONAL		RPL EXT F CONN			CE	CUSTOMER EDUC	438	DISCO IN ERROR
41	OM	417	ONE MOMENT PLS		REPL TIVO Q			BD	BURY DROP	911	DISCONNECT A/O
42	IM	051434082	INTR MOD NO ACQ		REF TO MAINT	REPL HM CAMERA		9T	9 TVS	902	DROP RECONNECT
43	MA	432434431	MDM NOT ACQUIRE		RPL EXT PASSIVE			MC	MUST-DO TC VIDEO	304	DROP PASSIVE
44	MA	051438432	MDM NOT ACQUIRE		REF TO MAINT			DA	DISASTER	903	DROP RELOCATE
45	NP		NO PICTURE					TP	TAP DISCONNECT	301	DROP/GNDBLK-TV
46	MA	039	MDM NOT ACQUIRE		TEL/HC NOT HOME			PR	PHONE RECONNECT	300	DROP/TAP-GNDBLK
47	MA		MDM NOT ACQUIRE					MR	MDVR PROBLEM	108	FEEDER ACTIVES
48	IM	434437	INTR MOD NO ACQ		ADD/REM TRAP	CANCEL BY OFFIC		MA	MDM NOT ACQUIRE	110	FEEDER CABLE
49	MS	437	MODEM SLW SPEED		RPL EXT F CONN			LS	LST/DIST SPEECH	109	FEEDER PASSIVES
50	MA		MDM NOT ACQUIRE					IN	INSTALLFOLLOWUP	111	FEEDERCONNECTOR
Sheet1

Cell Formulas
Range	Formula
E2:E50	E2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(LEFT(B2,3)),J:K,2,0)),"")
F2:F50	F2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(MID(B2,6,3)),J:K,2,0)),"")
G2:G50	G2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(RIGHT(B2,3)),J:K,2,0)),"")
C2:C50	C2	=VLOOKUP(LEFT(A2,2),H:I,2,0)

 

[etaf]

i have used TRIM and it works, the errors appear to be where the code does not exist at all

Sorry am i missing something

I have highlighted codes in A that even with a trim do not appear in column H

Book14-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	TC REASON	COMPL CDE	REASON DESCPT	REASON DESCPT 2	FIX CD	FIX CD 2	FIX CD 3	REASON KEY		Charge Back Code	Charge Back Description
2	WN	151	WIFI NOT WKG					TB	BUS TBL CALL	165	-48 V FAILURE	1
3	WN	151	WIFI NOT WKG					1T	1 TV	906	A/B SWITCH	1
4	CVIN	419	#N/A					EO	ECHO	904	A/O INSTALLED	1
5	CVIN	419	#N/A					SV	SAVE PROGRAM	905	A/O RECONNECTED	1
6	DP	453	#N/A					SD	SATELLITE DISH	312	ADD HSE AMP	1
7	DP	453	#N/A					PV	PPV NOT WORKING	434	ADD/REM TRAP	1
8	MA	354450439	MDM NOT ACQUIRE		CHNG ROUTER LOC			MS	MODEM SLW SPEED	404	ADJUST TV SET	1
9	EN	435	#N/A					OM	ONE MOMENT PLS	405	ADJUST VCR	1
10	MA	370	MDM NOT ACQUIRE					NP	NO PICTURE	311	ANIMAL CHEW	1
11	MA	370	MDM NOT ACQUIRE					HC	HSD NEW CONNECT	503	AUDIT ERROR	1
12	MA	370	MDM NOT ACQUIRE					TO	TIVO	114	CABLE SUCK OUT	1
13	MA	370	MDM NOT ACQUIRE					TH	HSD TROUBLE CALL	12	CANCEL @ PRECLL	1
14	MA	370	MDM NOT ACQUIRE					PC	PHONE NEW CONNECT	5	CANCEL AT DOOR	1
15	OM	429	ONE MOMENT PLS					QI	MUST-DO TC HSD DTA	34	CANCEL AT DOOR$	1
16	MA	432	MDM NOT ACQUIRE					7T	7 TVS	11	CANCEL BY OFFCE	1
17	AO	50	#N/A					DC	DAMAGED CABLE	7	CANCEL BY OFFIC	1
18	TO	428	TIVO					VO	VOD NOT WORKING	8	CANCEL@PRECALL	1
19	MA	51439	MDM NOT ACQUIRE					DT	DTA PROBLEM	10	CANCELLD BY SUB	1
20	IM	151	#N/A					EI	EMTA INT BL SNC	6	CANCELLD BY SUB	1
21	IM	151	#N/A					BP	BUS PHONE	354	CHNG ROUTER LOC	1
22	MA	439	MDM NOT ACQUIRE					3T	3 TVS	166	CMTS	1
23	MA	431451	MDM NOT ACQUIRE					GH	GHOSTING	134	COMM POWER	1
24	MA	151	MDM NOT ACQUIRE					H5	HM CAMERA	102	COMML PWR FAIL	1
25	MA	438	MDM NOT ACQUIRE		DISCO IN ERROR		DISCO IN ERROR	SI	SELF INSTALLATION	350	CONF WLESS CST$	1
26	MA	438	MDM NOT ACQUIRE		DISCO IN ERROR		DISCO IN ERROR	8T	8 TVS	117	CRACKED SHIELD	1
27	MA	151	MDM NOT ACQUIRE					DD	DWN OR LOW DROP	38	CUST EDUCATION$	1
28	MA	430	MDM NOT ACQUIRE					VP	VCR PROBLEM	36	CUST INSTAL EQ$	1
29	MA	430	MDM NOT ACQUIRE					RC	REMOTE PROBLEM	407	CUST INSTALD EQ	1
30	MA	51	MDM NOT ACQUIRE					IP	INTERMITTENT PRB	218	CUST PHONE PROB	1
31	MA	51	MDM NOT ACQUIRE					SG	SIGNAL LEAK - HOME	400	CUSTOMER EDUCAT	1
32	MA	D30	MDM NOT ACQUIRE					WN	WIFI NOT WKG	146	CUT/DAMAGED FIB	1
33	SC	51	#N/A					LW	LOW CHAN PROB	120	CUT/DMGD FIBER	1
34	DP	50	#N/A					CM	CUSTOMER IMPACT MAINT	D30	D3 SWAP	1
35	DP	50	#N/A					AU	FIELD AUDIT	406	DEFECTVE A/B SW	1
36	MA	51453	MDM NOT ACQUIRE					CV	CNV/DCT PROBLEM	306	DIRECT P/U DROP	1
37	IM	434432	#N/A		ADD/REM TRAP			CI	CLI	402	DIRECTP/USUB EQ	1
38	AO	437	#N/A					BI	BUS INTERNET	910	DISCO AT TAP	1
39	OM	417	ONE MOMENT PLS					BS	BUS SITE SURVEY	500	DISCO IN ERROR	1
40	AO	437	#N/A					CE	CUSTOMER EDUC	438	DISCO IN ERROR	1
41	OM	417	ONE MOMENT PLS					BD	BURY DROP	911	DISCONNECT A/O	1
42	IM	51434082	#N/A					9T	9 TVS	902	DROP RECONNECT	1
43	MA	432434431	MDM NOT ACQUIRE					MC	MUST-DO TC VIDEO	304	DROP PASSIVE	1
44	MA	51438432	MDM NOT ACQUIRE					DA	DISASTER	903	DROP RELOCATE	1
45	NP		NO PICTURE					TP	TAP DISCONNECT	301	DROP/GNDBLK-TV	1
46	MA	39	MDM NOT ACQUIRE					PR	PHONE RECONNECT	300	DROP/TAP-GNDBLK	1
47	MA		MDM NOT ACQUIRE					MR	MDVR PROBLEM	108	FEEDER ACTIVES	1
48	IM	434437	#N/A		ADD/REM TRAP	CANCEL BY OFFIC		MA	MDM NOT ACQUIRE	110	FEEDER CABLE	1
49	MS	437	MODEM SLW SPEED					LS	LST/DIST SPEECH	109	FEEDER PASSIVES	1
50	MA		MDM NOT ACQUIRE					IN	INSTALLFOLLOWUP	111	FEEDERCONNECTOR	1
Sheet1

Cell Formulas
Range	Formula
E2:E50	E2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(LEFT(B2,3)),J:K,2,0)),"")
F2:F50	F2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(MID(B2,6,3)),J:K,2,0)),"")
G2:G50	G2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(RIGHT(B2,3)),J:K,2,0)),"")
C2:C50	C2	=VLOOKUP(TRIM(A2),H:I,2,0)
L2:L50	L2	=COUNTIF(H:H,H2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=COUNTIF(H:H,TRIM(A1))=0	text	NO

 

[tispivey]

The format isn't the same here as it is in excel. If I could post the whole spreadsheet, I could explain it more.

 

[etaf]

if the sample is not representative of the issues and different on the real sheet ??
or are you saying XL2BB is the issue

if so , then use dropbox/onedrive and post a link to the file

 

[tispivey]

The xlb2bb didn't replicate the issue. I will make a copy of it and post it in a bit.

 

[etaf]

ok, i'm in UK and very late now - 01:12 - so i wont reply until later tomorrow now UK timezone

 

[tispivey]

Let me know if you are able to download it from here.

Test1234.xlsx

Sheet1 TC REASON,COMPL CDE,REASON DESCPT,REASON DESCPT 2,FIX CD,FIX CD 2,FIX CD 3,REASON KEY,Charge Back Code,Charge Back Description WN ,151 ,WIFI NOT WKG,RE-AUTH WIFI,,,TB,BUS TBL CALL,165,-48 V FAILURE WN ,151 ,WIFI NOT WKG,RE-AUTH WIFI,,,1T,1 TV,906,A/B ...

docs.google.com

 

[etaf]

i have downloaded ,
not sure now i understand the issue - like xl2bb , i get the same results with the actual shared file

The formulas I have, pulls the code from A, compares it to H, and returns what is in I into C. When I attempt to pull the rows that have multiple codes, and populate into D, it gives me an error because there are spaces at the end of the code in A. If I manually remove them, it works, but when I attempt to add TRIM, it doesn't do anything, so I get an error.

if i do a trim , it works, where it does not work is where the code in A does not match any code in H
same for G & J

I have highlighted (using conditional formatting) the codes in A and B that do not match

i have attached the file here in XL2B & on dropbox share

if you could pick some examples where the code does not match and explain what you expect to happen
I suspect it maybe you say CODES , plural , and that maybe more than i 2 character code is enter

Highlighted in column A

CVIN
MAWN
AOTO
etc

should these be 2 codes
CV
IN
MA
WN
AO
TO

and is that the issue - NOT the spaces

Dropbox share

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Part of the file in XL2BB

Test1234.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	TC REASON	COMPL CDE	REASON DESCPT	REASON DESCPT 2	FIX CD	FIX CD 2	FIX CD 3	REASON KEY		Charge Back Code	Charge Back Description
2	WN	151	RE-AUTH WIFI		RE-AUTH WIFI		RE-AUTH WIFI	TB	BUS TBL CALL	165	-48 V FAILURE
3	WN	151	RE-AUTH WIFI		RE-AUTH WIFI			1T	1 TV	906	A/B SWITCH
4	CVIN	419	REPL TIVO MINI		REPL TIVO MINI			EO	ECHO	904	A/O INSTALLED
5	CVIN	419	REPL TIVO MINI		REPL TIVO MINI			SV	SAVE PROGRAM	905	A/O RECONNECTED
6	DP	453	RPL INT PASSIVE		RPL INT PASSIVE			SD	SATELLITE DISH	312	ADD HSE AMP
7	DP	453	RPL INT PASSIVE		RPL INT PASSIVE			PV	PPV NOT WORKING	434	ADD/REM TRAP
8	MA	354450439	#N/A		CHNG ROUTER LOC	UNAUTH HOOK UP$		MS	MODEM SLW SPEED	404	ADJUST TV SET
9	EN	435	RPL EXT CORD		RPL EXT CORD			OM	ONE MOMENT PLS	405	ADJUST VCR
10	MA	370	REPLACE EMTA SL		REPLACE EMTA SL			NP	NO PICTURE	311	ANIMAL CHEW
11	MA	370	REPLACE EMTA SL		REPLACE EMTA SL			HC	HSD NEW CONNECT	503	AUDIT ERROR
12	MA	370	REPLACE EMTA SL		REPLACE EMTA SL			TO	TIVO	114	CABLE SUCK OUT
13	MA	370	REPLACE EMTA SL		REPLACE EMTA SL			TH	HSD TROUBLE CALL	12	CANCEL @ PRECLL
14	MA	370	REPLACE EMTA SL		REPLACE EMTA SL			PC	PHONE NEW CONNECT	5	CANCEL AT DOOR
15	OM	429	TIVO SVC ACCT		TIVO SVC ACCT			QI	MUST-DO TC HSD DTA	34	CANCEL AT DOOR$
16	MA	432	RPL EXT PASSIVE		RPL EXT PASSIVE			7T	7 TVS	11	CANCEL BY OFFCE
17	AO	050	REF TO INSTALL		REF TO INSTALL			DC	DAMAGED CABLE	7	CANCEL BY OFFIC
18	TO	428	REPR TIVO BDBND		REPR TIVO BDBND			VO	VOD NOT WORKING	8	CANCEL@PRECALL
19	MA	051439	#N/A		REF TO MAINT			DT	DTA PROBLEM	10	CANCELLD BY SUB
20	IM	151	RE-AUTH WIFI		RE-AUTH WIFI			EI	EMTA INT BL SNC	6	CANCELLD BY SUB
21	IM	151	RE-AUTH WIFI		RE-AUTH WIFI			BP	BUS PHONE	354	CHNG ROUTER LOC
22	MA	439	RPL GROUND BLCK		RPL GROUND BLCK			3T	3 TVS	166	CMTS
23	MA	431451	#N/A		REPAIR EXT DRP	NO PROBLEM FND		GH	GHOSTING	134	COMM POWER
24	MA	151	RE-AUTH WIFI		RE-AUTH WIFI			H5	HM CAMERA	102	COMML PWR FAIL
25	MA	438	DISCO IN ERROR		DISCO IN ERROR			SI	SELF INSTALLATION	350	CONF WLESS CST$
26	MA	438	DISCO IN ERROR		DISCO IN ERROR			8T	8 TVS	117	CRACKED SHIELD
27	MA	151	RE-AUTH WIFI		RE-AUTH WIFI			DD	DWN OR LOW DROP	38	CUST EDUCATION$
28	MA	430	REPLACE EXT DRP		REPLACE EXT DRP			VP	VCR PROBLEM	36	CUST INSTAL EQ$
29	MA	430	REPLACE EXT DRP		REPLACE EXT DRP			RC	REMOTE PROBLEM	407	CUST INSTALD EQ
30	MA	051	REF TO MAINT		REF TO MAINT			IP	INTERMITTENT PRB	218	CUST PHONE PROB
31	MA	051	REF TO MAINT		REF TO MAINT			SG	SIGNAL LEAK - HOME	400	CUSTOMER EDUCAT
32	MA	D30	#VALUE!					WN	WIFI NOT WKG	146	CUT/DAMAGED FIB
33	SC	051	REF TO MAINT		REF TO MAINT			LW	LOW CHAN PROB	120	CUT/DMGD FIBER
34	DP	050	REF TO INSTALL		REF TO INSTALL			CM	CUSTOMER IMPACT MAINT	D30	D3 SWAP
35	DP	050	REF TO INSTALL		REF TO INSTALL			AU	FIELD AUDIT	406	DEFECTVE A/B SW
36	MA	051453	#N/A		REF TO MAINT	NO ADULT		CV	CNV/DCT PROBLEM	306	DIRECT P/U DROP
37	IM	434432	#N/A		ADD/REM TRAP	OK ON TEST SET		CI	CLI	402	DIRECTP/USUB EQ
38	AO	437	RPL EXT F CONN		RPL EXT F CONN			BI	BUS INTERNET	910	DISCO AT TAP
39	OM	417	REPL TIVO Q		REPL TIVO Q			BS	BUS SITE SURVEY	500	DISCO IN ERROR
40	AO	437	RPL EXT F CONN		RPL EXT F CONN			CE	CUSTOMER EDUC	438	DISCO IN ERROR
41	OM	417	REPL TIVO Q		REPL TIVO Q			BD	BURY DROP	911	DISCONNECT A/O
42	IM	051434082	#N/A		REF TO MAINT	REPL HM CAMERA		9T	9 TVS	902	DROP RECONNECT
43	MA	432434431	#N/A		RPL EXT PASSIVE			MC	MUST-DO TC VIDEO	304	DROP PASSIVE
44	MA	051438432	#N/A		REF TO MAINT			DA	DISASTER	903	DROP RELOCATE
45	NP		#VALUE!					TP	TAP DISCONNECT	301	DROP/GNDBLK-TV
46	MA	039	TEL/HC NOT HOME		TEL/HC NOT HOME			PR	PHONE RECONNECT	300	DROP/TAP-GNDBLK
47	MA		#VALUE!					MR	MDVR PROBLEM	108	FEEDER ACTIVES
48	IM	434437	#N/A		ADD/REM TRAP	CANCEL BY OFFIC		MA	MDM NOT ACQUIRE	110	FEEDER CABLE
49	MS	437	RPL EXT F CONN		RPL EXT F CONN			LS	LST/DIST SPEECH	109	FEEDER PASSIVES
50	MA		#VALUE!					IN	INSTALLFOLLOWUP	111	FEEDERCONNECTOR
51	MA		#VALUE!					IM	INTR MOD NO ACQ	149	FIBER - OTHER
52	MA	439	RPL GROUND BLCK		RPL GROUND BLCK			IW	INSIDE PHN WIRE	118	FIBER ACTIVE
53	MA		#VALUE!					IR	INSTALL REPAIR	144	FIBER ACTIVE
54	MA		#VALUE!					LC	LOCATE	145	FIBER PASSIVE
55	MA	439	RPL GROUND BLCK		RPL GROUND BLCK			PT	PHONE RESTART	119	FIBER PASSIVE
56	MA	051	REF TO MAINT		REF TO MAINT			MH	MUST-DO TC HSD	302	GROUNDING SYSTM
57	WN		#VALUE!					PP	BUS PHONE PRE-WIRE	800	H/E OUTAGE PROB
58	WN		#VALUE!					MO	MACRO CHANGE	801	H/E RECEPTION
59	MA	051	REF TO MAINT		REF TO MAINT			QZ	MUST-DO TC PHN DTA	805	HE -48V FAILURE
60	MAWN		#VALUE!					QD	MUST-DO TC VID DTA	804	HE GEN FAILURE
61	MA	051	REF TO MAINT		REF TO MAINT			QH	HSD TRBL CALL DTA	164	HE GEN FAILURE
62	OM	417	REPL TIVO Q		REPL TIVO Q			QY	PHN TRBL CALL DTA	160	HE OUTAGE PROB
63	DP	290	RPL REMOTE CTRL		RPL REMOTE CTRL			RS	RESTART SERVICE	161	HE RECEPTION
64	DP	290	RPL REMOTE CTRL		RPL REMOTE CTRL			PS	PHONE UPGRADE	803	HE UPS FAILURE
65	AOTO	290	RPL REMOTE CTRL		RPL REMOTE CTRL			LP	LINES IN PICTUR	163	HE UPS FAILURE
66	AOTO	290	RPL REMOTE CTRL		RPL REMOTE CTRL			AO	ADDITIONAL	414	HM CUST EDUC
67	IM	051	REF TO MAINT		REF TO MAINT			CP	CHK PHONE LINE	412	HM FMWARE UPGRD
68	MA		#VALUE!					AP	AUDIO PROBLEM	912	HOOK UP VCR
69	MA	434151	#N/A		ADD/REM TRAP	NO PROBLEM FND		CO	CHANGE OVER	703	ILLEGAL HOOKUP
70	MA	434151	#N/A		ADD/REM TRAP	NO PROBLEM FND		SC	SNGL CHANL OUT	502	IMPROPER INSTAL
71	MA	434360	#N/A		ADD/REM TRAP			TC	TROUBLE CALL	504	IMPROPER TAG
72	IMMS	453	RPL INT PASSIVE		RPL INT PASSIVE			NC	NEW CONNECT	201	INSTALL CNVRTER
73	IMMS	453	RPL INT PASSIVE		RPL INT PASSIVE			NM	NONCUSTOMER IMPACTING MAINT	353	INSTLLD AC ROUT
74	IM	051432	#N/A		REF TO MAINT	OK ON TEST SET		MY	MUST-DO TC PHONE	351	INSTLLD EXTNDRS
75	IM	051432	#N/A		REF TO MAINT	OK ON TEST SET		H2	HDTV 201-500	148	LEVEL ADJST-FIB
76	MA	051434	#N/A		REF TO MAINT	NOTHOME/DOORHNG		6T	6 TVS	138	LEVEL ADJST-HFC
77	MA	051434	#N/A		REF TO MAINT	NOTHOME/DOORHNG		VD	VOLUNTARY DISCO	112	LEVEL ADJUSTMEN
78	MA	051434	#N/A		REF TO MAINT	NOTHOME/DOORHNG		VU	VCR HOOKUP	113	LSE SEIZURESCRW
79	MA	430	REPLACE EXT DRP		REPLACE EXT DRP			DV	DVR PROBLEM	701	NEIGHBOR TAMPER
80	MA	430	REPLACE EXT DRP		REPLACE EXT DRP			2T	2 TVS	3	NO ADULT
81	MA	430	REPLACE EXT DRP		REPLACE EXT DRP			FP	FLASHING PICTUR	32	NO ADULT$
82	IM		#VALUE!					QC	TROUBLE CALL DTA	30	NO PROB FOUND$
83	IM	360432439434	#N/A		REPLACE WL MDM			EN	EMTA NO BLK SNC	1	NO PROBLEM FND
84	MA	051367	#N/A		REF TO MAINT	CANCEL BY OFFIC		DU	DVR	33	NOTHME/DOORHNG$
Sheet1

Cell Formulas
Range	Formula
E2:E84	E2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(LEFT(B2,3)),J:K,2,0)),"")
F2:F84	F2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(MID(B2,6,3)),J:K,2,0)),"")
G2:G84	G2	=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(RIGHT(B2,3)),J:K,2,0)),"")
C2:C84	C2	=VLOOKUP(TRIM(B2)*1,J:K,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=COUNTIF($J:$J,TRIM(B1))=0	text	NO
A:A	Expression	=COUNTIF($H:$H,TRIM(A1))=0	text	NO

 

[tispivey]

I found a formula that works for column D.

=IFERROR(VLOOKUP(MID((SUBSTITUTE($A4," ","")),3,2),$H:$I,2,0),"")

Now I am just trying to recreate it in G.

=IFERROR(IF(VALUE(B2)=$J$1," ",VLOOKUP(VALUE(RIGHT(B2,3)),J:K,2,0)),"")

 

[tispivey]

Is there a way to add "SUBSTITUTE" to the bottom formula, and it work for this, it seems like it should, but I can't get it to work properly.