| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to match output from 2 columns...
We have 1500 clients plugged into 50 switches throught our
BAN. I have done an nbtstat -a on all 25 of our networks and put the results into column B. Column A has the results from a 'sh cam dynamic' on all 50 switches. I need a macro to search thru column B and column A to put the results in Column C-F. Basically I need to know the what workstation,username,mac, and vlan that is plugged into my switch ports. Thanks for your help.... Natla |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to match output from 2 columns...
Natla,
It would be best if you posted examples of your data, and how the extract would look based on that data. HTH, Bernie MS Excel MVP " wrote in message ... We have 1500 clients plugged into 50 switches throught our BAN. I have done an nbtstat -a on all 25 of our networks and put the results into column B. Column A has the results from a 'sh cam dynamic' on all 50 switches. I need a macro to search thru column B and column A to put the results in Column C-F. Basically I need to know the what workstation,username,mac, and vlan that is plugged into my switch ports. Thanks for your help.... Natla |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to match output from 2 columns...
column A
216 00-08-74-bb-3b-b9 2/31 [bLL] 216 00-08-74-bb-40-b5 2/36 [bLL] 216 00-b0-b0-11-7b-6b 2/30 [bLL] 216 00-b0-b0-11-8b-70 2/47 [bLL] 216 00-08-74-bb-94-8b 3/25 [bLL] 216 00-08-74-bb-95-b3 3/46 [bLL] 216 00-08-74-bb-97-39 3/27 [bLL] 217 00-08-74-bb-84-64 4/25 [bLL] 217 00-08-74-bb-85-50 4/47 [bLL] 217 00-08-74-bb-41-94 5/31 [bLL] 217 00-08-74-b8-03-b9 5/9 [bLL] 217 00-b0-b0-11-8b-77 6/35 [bLL] 500 00-01-b6-3b-bb-bb 6/34 [bLL] colum B NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002476 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002476 <20 UNIQUE Registered WRKSTTN002476 <03 UNIQUE Registered WRKSTTN002476$ <03 UNIQUE Registered USEAS <1E GROUP Registered JIDY.MINICHING <03 UNIQUE Registered MAC Address = 00-b0-b0-11-8b-70 H:\nbtstat -a x.xx.14.33 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WLMECH000137 <00 UNIQUE Registered USEAS <00 GROUP Registered WLMECH000137 <20 UNIQUE Registered WLMECH000137 <03 UNIQUE Registered USEAS <1E GROUP Registered MAC Address = 00-08-74-bb-97-39 H:\nbtstat -a x.xx.14.34 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] Host not found. H:\nbtstat -a x.xx.14.35 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002478 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002478 <20 UNIQUE Registered WRKSTTN002478 <03 UNIQUE Registered USEAS <1E GROUP Registered WRKSTTN002478$ <03 UNIQUE Registered GEORGE.NORS <03 UNIQUE Registered MAC Address = 00-08-74-bb-41-94 Hope that helps! Thanks Natla -----Original Message----- Natla, It would be best if you posted examples of your data, and how the extract would look based on that data. HTH, Bernie MS Excel MVP " wrote in message ... We have 1500 clients plugged into 50 switches throught our BAN. I have done an nbtstat -a on all 25 of our networks and put the results into column B. Column A has the results from a 'sh cam dynamic' on all 50 switches. I need a macro to search thru column B and column A to put the results in Column C-F. Basically I need to know the what workstation,username,mac, and vlan that is plugged into my switch ports. Thanks for your help.... Natla . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to match output from 2 columns...
So you would do you match on the
00-b0-b0-11-8b-70 parts of MAC Address = 00-b0-b0-11-8b-70 and 217 00-b0-b0-11-8b-77 6/35 [bLL] Is the data above the MAC line the data you want? Are there a fixed number of row between MAC lines? What is the data that you want, and what is extraneous? Is this the basic unit of data: NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002476 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002476 <20 UNIQUE Registered WRKSTTN002476 <03 UNIQUE Registered WRKSTTN002476$ <03 UNIQUE Registered USEAS <1E GROUP Registered JIDY.MINICHING <03 UNIQUE Registered MAC Address = 00-b0-b0-11-8b-70 H:\nbtstat -a x.xx.14.33 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] What can be taken out of this and thrown away? HTH, Bernie MS Excel MVP wrote in message ... column A 216 00-08-74-bb-3b-b9 2/31 [bLL] 216 00-08-74-bb-40-b5 2/36 [bLL] 216 00-b0-b0-11-7b-6b 2/30 [bLL] 216 00-b0-b0-11-8b-70 2/47 [bLL] 216 00-08-74-bb-94-8b 3/25 [bLL] 216 00-08-74-bb-95-b3 3/46 [bLL] 216 00-08-74-bb-97-39 3/27 [bLL] 217 00-08-74-bb-84-64 4/25 [bLL] 217 00-08-74-bb-85-50 4/47 [bLL] 217 00-08-74-bb-41-94 5/31 [bLL] 217 00-08-74-b8-03-b9 5/9 [bLL] 217 00-b0-b0-11-8b-77 6/35 [bLL] 500 00-01-b6-3b-bb-bb 6/34 [bLL] colum B NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002476 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002476 <20 UNIQUE Registered WRKSTTN002476 <03 UNIQUE Registered WRKSTTN002476$ <03 UNIQUE Registered USEAS <1E GROUP Registered JIDY.MINICHING <03 UNIQUE Registered MAC Address = 00-b0-b0-11-8b-70 H:\nbtstat -a x.xx.14.33 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WLMECH000137 <00 UNIQUE Registered USEAS <00 GROUP Registered WLMECH000137 <20 UNIQUE Registered WLMECH000137 <03 UNIQUE Registered USEAS <1E GROUP Registered MAC Address = 00-08-74-bb-97-39 H:\nbtstat -a x.xx.14.34 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] Host not found. H:\nbtstat -a x.xx.14.35 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002478 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002478 <20 UNIQUE Registered WRKSTTN002478 <03 UNIQUE Registered USEAS <1E GROUP Registered WRKSTTN002478$ <03 UNIQUE Registered GEORGE.NORS <03 UNIQUE Registered MAC Address = 00-08-74-bb-41-94 Hope that helps! Thanks Natla -----Original Message----- Natla, It would be best if you posted examples of your data, and how the extract would look based on that data. HTH, Bernie MS Excel MVP " wrote in message ... We have 1500 clients plugged into 50 switches throught our BAN. I have done an nbtstat -a on all 25 of our networks and put the results into column B. Column A has the results from a 'sh cam dynamic' on all 50 switches. I need a macro to search thru column B and column A to put the results in Column C-F. Basically I need to know the what workstation,username,mac, and vlan that is plugged into my switch ports. Thanks for your help.... Natla . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to match output from 2 columns...
Bernie,
Would I be able to email the excel spreadsheet? I think that would be the best way for you to see what I am trying to do... Natla -----Original Message----- So you would do you match on the 00-b0-b0-11-8b-70 parts of MAC Address = 00-b0-b0-11-8b-70 and 217 00-b0-b0-11-8b-77 6/35 [bLL] Is the data above the MAC line the data you want? Are there a fixed number of row between MAC lines? What is the data that you want, and what is extraneous? Is this the basic unit of data: NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002476 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002476 <20 UNIQUE Registered WRKSTTN002476 <03 UNIQUE Registered WRKSTTN002476$ <03 UNIQUE Registered USEAS <1E GROUP Registered JIDY.MINICHING <03 UNIQUE Registered MAC Address = 00-b0-b0-11-8b-70 H:\nbtstat -a x.xx.14.33 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] What can be taken out of this and thrown away? HTH, Bernie MS Excel MVP wrote in message ... column A 216 00-08-74-bb-3b-b9 2/31 [bLL] 216 00-08-74-bb-40-b5 2/36 [bLL] 216 00-b0-b0-11-7b-6b 2/30 [bLL] 216 00-b0-b0-11-8b-70 2/47 [bLL] 216 00-08-74-bb-94-8b 3/25 [bLL] 216 00-08-74-bb-95-b3 3/46 [bLL] 216 00-08-74-bb-97-39 3/27 [bLL] 217 00-08-74-bb-84-64 4/25 [bLL] 217 00-08-74-bb-85-50 4/47 [bLL] 217 00-08-74-bb-41-94 5/31 [bLL] 217 00-08-74-b8-03-b9 5/9 [bLL] 217 00-b0-b0-11-8b-77 6/35 [bLL] 500 00-01-b6-3b-bb-bb 6/34 [bLL] colum B NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002476 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002476 <20 UNIQUE Registered WRKSTTN002476 <03 UNIQUE Registered WRKSTTN002476$ <03 UNIQUE Registered USEAS <1E GROUP Registered JIDY.MINICHING <03 UNIQUE Registered MAC Address = 00-b0-b0-11-8b-70 H:\nbtstat -a x.xx.14.33 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WLMECH000137 <00 UNIQUE Registered USEAS <00 GROUP Registered WLMECH000137 <20 UNIQUE Registered WLMECH000137 <03 UNIQUE Registered USEAS <1E GROUP Registered MAC Address = 00-08-74-bb-97-39 H:\nbtstat -a x.xx.14.34 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] Host not found. H:\nbtstat -a x.xx.14.35 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002478 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002478 <20 UNIQUE Registered WRKSTTN002478 <03 UNIQUE Registered USEAS <1E GROUP Registered WRKSTTN002478$ <03 UNIQUE Registered GEORGE.NORS <03 UNIQUE Registered MAC Address = 00-08-74-bb-41-94 Hope that helps! Thanks Natla -----Original Message----- Natla, It would be best if you posted examples of your data, and how the extract would look based on that data. HTH, Bernie MS Excel MVP " wrote in message ... We have 1500 clients plugged into 50 switches throught our BAN. I have done an nbtstat -a on all 25 of our networks and put the results into column B. Column A has the results from a 'sh cam dynamic' on all 50 switches. I need a macro to search thru column B and column A to put the results in Column C-F. Basically I need to know the what workstation,username,mac, and vlan that is plugged into my switch ports. Thanks for your help.... Natla . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to match output from 2 columns...
Natla,
Okay, but I'm leaving for the weekend... You'll need to fix my email address - take out spaces and replace dot with . Bernie MS Excel MVP "Natla22" wrote in message ... Bernie, Would I be able to email the excel spreadsheet? I think that would be the best way for you to see what I am trying to do... Natla -----Original Message----- So you would do you match on the 00-b0-b0-11-8b-70 parts of MAC Address = 00-b0-b0-11-8b-70 and 217 00-b0-b0-11-8b-77 6/35 [bLL] Is the data above the MAC line the data you want? Are there a fixed number of row between MAC lines? What is the data that you want, and what is extraneous? Is this the basic unit of data: NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002476 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002476 <20 UNIQUE Registered WRKSTTN002476 <03 UNIQUE Registered WRKSTTN002476$ <03 UNIQUE Registered USEAS <1E GROUP Registered JIDY.MINICHING <03 UNIQUE Registered MAC Address = 00-b0-b0-11-8b-70 H:\nbtstat -a x.xx.14.33 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] What can be taken out of this and thrown away? HTH, Bernie MS Excel MVP wrote in message ... column A 216 00-08-74-bb-3b-b9 2/31 [bLL] 216 00-08-74-bb-40-b5 2/36 [bLL] 216 00-b0-b0-11-7b-6b 2/30 [bLL] 216 00-b0-b0-11-8b-70 2/47 [bLL] 216 00-08-74-bb-94-8b 3/25 [bLL] 216 00-08-74-bb-95-b3 3/46 [bLL] 216 00-08-74-bb-97-39 3/27 [bLL] 217 00-08-74-bb-84-64 4/25 [bLL] 217 00-08-74-bb-85-50 4/47 [bLL] 217 00-08-74-bb-41-94 5/31 [bLL] 217 00-08-74-b8-03-b9 5/9 [bLL] 217 00-b0-b0-11-8b-77 6/35 [bLL] 500 00-01-b6-3b-bb-bb 6/34 [bLL] colum B NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002476 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002476 <20 UNIQUE Registered WRKSTTN002476 <03 UNIQUE Registered WRKSTTN002476$ <03 UNIQUE Registered USEAS <1E GROUP Registered JIDY.MINICHING <03 UNIQUE Registered MAC Address = 00-b0-b0-11-8b-70 H:\nbtstat -a x.xx.14.33 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WLMECH000137 <00 UNIQUE Registered USEAS <00 GROUP Registered WLMECH000137 <20 UNIQUE Registered WLMECH000137 <03 UNIQUE Registered USEAS <1E GROUP Registered MAC Address = 00-08-74-bb-97-39 H:\nbtstat -a x.xx.14.34 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] Host not found. H:\nbtstat -a x.xx.14.35 Local Area Connection: Node IpAddress: [x.xx.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WRKSTTN002478 <00 UNIQUE Registered USEAS <00 GROUP Registered WRKSTTN002478 <20 UNIQUE Registered WRKSTTN002478 <03 UNIQUE Registered USEAS <1E GROUP Registered WRKSTTN002478$ <03 UNIQUE Registered GEORGE.NORS <03 UNIQUE Registered MAC Address = 00-08-74-bb-41-94 Hope that helps! Thanks Natla -----Original Message----- Natla, It would be best if you posted examples of your data, and how the extract would look based on that data. HTH, Bernie MS Excel MVP " wrote in message ... We have 1500 clients plugged into 50 switches throught our BAN. I have done an nbtstat -a on all 25 of our networks and put the results into column B. Column A has the results from a 'sh cam dynamic' on all 50 switches. I need a macro to search thru column B and column A to put the results in Column C-F. Basically I need to know the what workstation,username,mac, and vlan that is plugged into my switch ports. Thanks for your help.... Natla . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| output row number for corresponding non-zero value of match functi | Excel Discussion (Misc queries) | |||
| Lookup 2 columns of data, perform, match, output results | Excel Worksheet Functions | |||
| how to compare two columns and get a output from it | Excel Worksheet Functions | |||
| Compare 2 columns and output to a 3rd column | Excel Discussion (Misc queries) | |||
| Excel VBA- Get user input, search the for match, output to text file | Excel Programming | |||
Linear Mode
