Macro Creation Help: Lookup & Substitute data in table

[jeffcoleky]

I need help creating a macro that will populate each row in column "C" with data from column "B", but with the following differences:

1. It should substitute the error codes with the lookup values in the error key "$E$3:$G$50".
2. It should ignore Error Codes (AS01), (AS16), and (AS17).
3. All other text in the cell should be copied over and the irrelevant parenthesis or commas should be ignored (See B4 for example).

Note: all error codes are contained within (), and if more than one error exists, a comma separates each one within the parenthesis.

Can anyone help me create this from scratch?

Sorry, i cannot paste the data here nicely below, my mrexcelhtml plugin is locking up my browser. You can download the sample (formatted) data here: http://www.filedropper.com/mailcodessampledata

(Windows 7, Office 2013 and newer)

	Data	Desired results		Key
#	Error Code	Decoded		Code	Meaning	Ignore
1	4613is invalid (AE10)	4613 is invalid (House/Building Number Invalid)		(AS01)	Address Verified	X
2	Address is Vacant (AS01,AS16,AS17)	Address is Vacant		(AC01)	ZIP Code
3	Address is Vacant (AS01,AS16)	Address is Vacant		(AC02)	State
4	(AS01)			(AC03)	City
5	(AS01)			(AC04)	Base/Alternate
6	(AC11,AS01)	(Suffix		(AC05)	Alias Name
7	Address is Vacant (AS01,AS16)	Address is Vacant		(AC06)	Address Swap
8	No Mail Delivery (AS01,AS17)	No Mail Delivery		(AC07)	Address1 & Company Swap
9	Address is Vacant (AS01,AS16)	Address is Vacant		(AC08)	Plus4
10	Address is Vacant (AS01,AS16,AS17)	Address is Vacant		(AC09)	Urbanization
11	Address is Vacant (AS01,AS16,AS17)	Address is Vacant		(AC10)	Street Name
12	Address is Vacant (AS01,AS16)	Address is Vacant		(AC11)	Suffix
13	Address is Vacant (AS01,AS16)	Address is Vacant		(AC12)	Directional
14	Suite/Apt number missing(AE09,AS02)	Suite/Apt number missing (Suite/Apartment Missing,Default Address)		(AC13)	Suite/Apartment Name
15	Address is Vacant (AS01,AS16)	Address is Vacant		(AC14)	Suite Range Change
16	(AC11,AS01)	(Suffix		(AE01)	Postal Code
17	No Mail Delivery (AS01,AS17)	No Mail Delivery		(AE02)	Unknown Street
18	(AC01,AS01)	(ZIP Code		(AE03)	Component
19	Address is Vacant (AS01,AS16,AS17)	Address is Vacant		(AE04)	Non-Deliverable
20	Address is Vacant (AS01,AS16)	Address is Vacant		(AE05)	Multiple Match
21	(AC11,AS01)	(Suffix		(AE06)	Early Warning System
22	Address is Vacant (AC01,AS01,AS16)	Address is Vacant (ZIP Code)		(AE07)	Minimum Address
23	Address is Vacant (AS01,AS16)	Address is Vacant		(AE08)	Suite/Apartment Invalid
24	11001is invalid (AE10)	11001 is invalid (House/Building Number Invalid)		(AE09)	Suite/Apartment Missing
25	Address is Vacant (AC11,AS01,AS16)	Address is Vacant (Suffix)		(AE10)	House/Building Number Invalid
26	Address is Vacant (AS01,AS16)	Address is Vacant		(AE11)	House/Building Number Missing
27	Address is Vacant (AS01,AS16)	Address is Vacant		(AE12)	Box Number Invalid
28	Address is Vacant (AS01,AS16)	Address is Vacant		(AE13)	Box Number Missing
29	Address is Vacant (AS01,AS16)	Address is Vacant		(AE14)	PMB number Missing
30	Address is Vacant (AS01,AS16)	Address is Vacant		(AE15)	Demo Mode
31	Address is Vacant (AS01,AS16)	Address is Vacant		(AE16)	Expired Database
32	Address is Vacant (AS01,AS16)	Address is Vacant		(AE17)	Suite/Apartment Not Required
33	Address is Vacant (AS01,AS16)	Address is Vacant		(AE18)	Extraneous Information
34	Address is Vacant (AS01,AS16)	Address is Vacant		(AE19)	Find Suggestion Timeout
35	Address is Vacant (AS01,AS16)	Address is Vacant		(AE20)	Find Suggestion Disabled
36	Address is Vacant (AS01,AS16)	Address is Vacant		(AS02)	Default Address
37	Address is Vacant (AS01,AS16,AS17)	Address is Vacant		(AS03)	NON-USPS Address
38	No Mail Delivery (AS01,AS17)	No Mail Delivery		(AS09)	Foreign Address
39	No Mail Delivery (AS01,AS17)	No Mail Delivery		(AS10)	CMRA Address
40	Address is Vacant (AS01,AS16)	Address is Vacant		(AS13)	Address Updated
41	(AS01)			(AS14)	Suite Appended
42	(AS01)			(AS15)	Apartment Appended
43	(AC01,AS01)	(ZIP Code		(AS16)	Vacant Address	X
44	(AC11,AS01)	(Suffix)		(AS17)	No Mail Delivery	X
45	Address is Vacant (AS01,AS16)	Address is Vacant		(AS18)	DPV Error
46	(AC11,AS01)	(Suffix)		(AS20)	USPS Delivery Only
47	Address is Vacant (AS01,AS16)	Address is Vacant		(AS22)	No Suggestion
48	Address is Vacant (AS01,AS16)	Address is Vacant		(AS23)	Extraneous Information
49	Address is Vacant (AS01,AS16)	Address is Vacant
50	(AC10,AS01)	(Street Name)
51	(AC01,AS01)	(ZIP Code
52	Address is Vacant (AS01,AS16)	Address is Vacant
53	(AC11,AS01)	(Suffix)
54	Address is Vacant (AS01,AS16)	Address is Vacant
55	Address is Vacant (AS01,AS16)	Address is Vacant
56	(AS01)
57	(AC11,AS01)	(Suffix)
58	Address is Vacant (AS01,AS16)	Address is Vacant
59	(AC05,AS01)	(Alias Name
60	(AC11,AS01)	(Suffix)
61	(AC11,AS01)	(Suffix)
62	(AC01,AS01)	(ZIP Code)
63	Address is Vacant (AS01,AS16)	Address is Vacant
64	(AC11,AS01)	(Suffix)
65	Address is Vacant (AS01,AS16)	Address is Vacant
66	Address is Vacant (AS01,AS16)	Address is Vacant
67	(AS01)
68
69
70
71
72
73
74

<tbody>
</tbody>