Find Matching Data in different columns of different worksheets
 

I want to find values of column B of Worksheet "NAT" in
the Column C of Worksheet "Policy". (Please note that
there is Many-to-many relationships between
Worksheets "NAT" to "Policy") Where we find the value, I
want to put a corresponding value from the column A of
Worksheet "NAT" into the column B of worksheet "Policy".

However, since the relationship is many-to-many, I have
to find the values in the column C from Wkst "NAT" to
Column D in the Wkst "Policy" for differentiation. In
some cases, instead of value, the alias (group) is
mentioned in the Column D of wkst "Policy". For such
cases, I have to find the value of Column C from
wkst "NAT" to the Column B (String format) of the Wkst
Groups.

Worksheet NAT:
Column A
Heading: Rule
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Column B
Heading: From
lexusnexus-ips
zionsgroup
333.DH.net
panixgroup
covadgroup
333.DH.net
333.DH.net
333.DH.net
333.DH.net
g_GEFS_as400_Clients
g_GEFS_as400_Clients
333.DH.net
333.DH.net
333.DH.net
333.DH.net
333.DH.net
333.DH.net
333.DH.net
333.DH.net
333.DH.net

Column C
Heading: To (String Format)
xlate_260.25.84.3
xlate_260.25.84.4
xlate_260.25.84.5
xlate_260.25.84.6
xlate_260.25.84.7
h260.25.244.6_eclipses2.x
h260.25.244.42_eclipses5.x
h260.25.245.88_eclipses4.x
gPDP_WestSt_Internet_Src_Hide
h000.43.56.2_as400_bkp.x
h000.43.56._as400_prim.x
h260.25.245.45_botcc.x
h260.25.245.46_botcc.x
gPDP_WestSt_VOR_Src_hide
2z
y
abc
xyz
z
zz

Worksheet: Policy
Column A
Heading: Number
1
2
3
4
5
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
50
51
52
53


Column B
Heading: Rule








Column C
Heading: From
gml.DH.net
gml.DH.net
gml.DH.net
gml.DH.net
gml.DH.net
gml.DH.net
Any
Any
Any
PDP_West_Routers
GCSC
PDP_West_Routers
PDP_West_Routers
PDP_West_Routers
PDP_West_Routers
gPDP_Nets
Any
gPDP_Nets
gPDP_Nets
g_DSM
gPDP_Nets
gPDP_Nets gNETIQ
gSSHClients h000.0.57.3_ds h60.5.6.9_ds h000.0.37.30
h000.0.57.30 h000.0.57.43 h60.5.6.7
Any
h60.5.84._wstutil5a_int n9..8.0_ssl_vpn_pool_tel
h000.43.38.9_telutil5b_ext
gml.DH.net
gml.DH.net
wst_inktomi
gml.DH.net
wst_IM_proxies
h60.5.000.3_ewstww0
gml.DH.net
h_69.4.5.0_hope_gre h_000.43.5.53_wst_gre
lexusnexus-ips
zionsgroup
gml.DH.net
covadgroup
panixgroup
gml.DH.net
gml.DH.net
Any
Any
gml.DH.net
lexusnexus-ips
zionsgroup
gml.DH.net
panixgroup


Column D
Heading: To
Group1
Group2
Group3
Group4
Group5
fuswest04 fuswest04a fuswest04b
fuswest04 fuswest04a fuswest04b
fuswest04 fuswest04a fuswest04b
broadcast.all multicast.all
PDP_West_Routers
PDP_West_Routers
GCSC
h999.40.999.7
h000.0.37.30 h60.5.6.7 h000.0.57.43 h000.0.57.30
h60.5.69.34 h60.5.69.35
gNTP-servers
gEXT_dns
gINT_dns
g_DSM
gPDP_Nets
gACE
gNETIQ gPDP_Nets
gEXT_dns
h60.5.84._wstutil5a_int
Any
h60.5.84._wstutil5a_int
wst_inktomi
wst_inktomi
gml.DH.net Net_63.95.4.0
wst_IM_proxies
gml.DH.net
gml.DH.net
h60.5.000.3_ewstww0
h_000.43.5.53_wst_gre h_69.4.5.0_hope_gre
xlate_60.5.84.3 h9.73.6.08_LexusNexus
h07.4.47.35_ZionsBank xlate_60.5.84.4
h04.6.43.33_sdctg xlate_60.5.84.5
gCovad_Telnet xlate_60.5.84.7
n66.84..0_PANIX panix66.84.. xlate_60.5.84.6
h60.5.44.6_eclipses.x h60.5.44.4_eclipses5.x
h60.5.45.88_eclipses4.x
h04.87.87.33_srp.na.blackberry.net
h06.5.6.33_srp.na.blackberry.net
Any
Any
h260.25.244.6_eclipses2.x
xlate_260.25.84.3
xlate_260.25.84.4
xlate_260.25.84.5
xlate_260.25.84.6

Worksheet: Groups
Column A
Heading: Group Name
covadgroup
fuswest04
g_costa_rica
g_DSM
g_GEFS_as400_Clients
g_Omgeo_Servers
gACE
gCovad_Telnet
GCSC
gEXT_dmzs
gEXT_dns
gINT_dmzs
gINT_dns
gml.DH.net
gMLX_DH_Hosts
gNETIQ
gNTP-servers
gPDP_Nets
gPDP_WestSt_Internet_Src_Hide
gPDP_WestSt_VOR_Src_hide
gSSHClients
zionsgroup
Group1
Group2
Group3
Group4
Group5

Column B
Heading: Members
imalik-ML-desktop n_covad_7.5.67.0
fuswest04a fuswest04b
n9.77.9.0_5 n9.77.9.4
h60.5.0.65 h69.4.5.0_dsm.hud h69.4.59.37_autosys
h30.0.0.54 n69.43.64.0
h58.4.6.88_botcc h58.4.30.88_botcc
h64.79.3.3 h69.4.50.3 h69.4.50.4 h69.4.50.9 h69.4.50.
h69.4.50.3 h69.4.50.4 h69.4.50.5
h60.5.5.70_mlwstfire0.ace h60.5.85.99_wstacepri_tst
h60.5.33.6_mlwfnfire0.ace
h67.00.45.6_covad h67.00.45.8_covad h67.00.45.67_covad
h67.00.67.6_covad
h35.89.5.0_rtppmt0 h35.89.5._drmsdh h35.89.5._drccdh
h60.5.68.97_rtpccdh h60.5.68.000_rtpmsdh
h60.5.68.40_vitalnet h60.5.69.3_taylor
n000.43.38.8_tel_ext_dmz n000.43.54.8_wst_ext_dmz
h000.43.3.95_teldns h000.43.3.96_teldd
h000.43.48.95_wstdns h000.43.48.96_wstdd
n60.5.84.3_tel_int_dmz n60.5.84.0_7-wst_int_dmz
h60.5.000._DNS h60.5.49.60_DNS h60.5.58.66_DNS
h69.4.07.65_DNS h69.4.8._dns h000.0.37.30 h000.0.57.30
n_08.0.43.0 n_65.96.5.0 n_dh0.0.0.0 n_dh.0.0.0
n_dh.6.46.0 n_dh.6.50.0 n_dh9.39.33.96 n_dh9.4.85.0-h
n_dh3.08.0.0 n_dh33.33.0.0_ml.Canada n_dh35.89.5.0
n_dh4.8.0.0 n_dh60.5.0.0 n_dh57.7.34.0_tcs
n_dh57.7.36.0_tcs n_dh65.77.0.0 n_dh69.4-43 n_dh999.40-43
n_dh7.0.0.0 n_dh7.6.0.0 n_dh9.58.0.0 n_dh9.68.0.0
n_dh9.77.0.0 n_dh000.0.0.0 n_dh000.53.64.0 n_dh9.0.0.0
n_dh30.0.0.0 n65.95.5.0_satyam
h60.5..65 h60.5.6.93 h60.5.6.65 h60.5.6.9 h60.5.63.
h60.5.99.45 h60.5.99.8 h60.5.49.7 h60.5.59.34 h69.40.64.9
h69.4.0.37 h69.4.0.34 h69.4.3.33 h69.4.3.34 h69.4.3.36
h69.4.3.38 h69.4.3.39 h69.4.3.40 h69.4.5.0 h69.4..05
h69.4.85.0 h69.4.85.49 h69.4.86.8 h69.43.03.3
DEVSYSMGT3_69.4.83.54 h0.60..6 h0.60.33.35 h0.60.50.5
h0.60..5
DB h60.5.34.30 h60.5.77.30 h60.5.77.3 h60.5.8.8 h60.5.6.6
h69.4.3._hudson_js h7.3.4.33 h7.3.4.34 h7.3.4.60_wfcn_tpa
h7.3.4.47 h9.77.3.76 h9.77.3.77 h9.77.36.73 h9.77.36.75
h9.77.36.76 h98.3.63.7 h000.0.6.7_ntpd h000.0.37.30
h000.0.57. h000.0.57.30 h000.0.57.43 h000.0.6.48_west_js
h06.43.7. h06.43.7.4_tel_js h06.43.7.6 n69.4.93.0
n69.4.94.0 n69.4.95.0 n69.4.96.0
gEXT_dmzs gINT_dmzs n_dh000.43.48.0 n60.5.000.0-55
n60.5.4.0 n000.43.3.0 n000.43.39.8 n06.43.3.0_wst
n06.43.4.0_tel
gEXT_dns h04.87.87.33_srp.na.blackberry.net
h06.5.6.33_srp.na.blackberry.net
g_Omgeo_Servers
DB telutil3a telutil3b wstutil3a wstutil3b
imalik-ML-desktop n_zions69.4.5.0
y
abc
xyz
Part of string z
Part of string zz



So after the comparison, the worksheet "Policy"'s columns
A and B (the first five and last five rows)should look
like this.

Worksheet "Policy"
Column A Column B

Number NAT
1 16
2 17
3 18
4 19
5 20
..
..
45 6
50 1
51 2
52 3
53 4