Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find matching data in two worksheet columns | Excel Worksheet Functions | |||
Find Matching Data Two Columns | Excel Worksheet Functions | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
Find Matching Records in Two Worksheets | Excel Discussion (Misc queries) | |||
Find matching records in two worksheets | Excel Discussion (Misc queries) |