Macro to match output from 2 columns...

N

Natla22

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
 
B

Bernie Deitrick

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
 
G

Guest

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
 
B

Bernie Deitrick

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

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

"[email protected]"


.
Click to expand...
Click to expand...
 
N

Natla22

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

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

"[email protected]"
Click to expand...
We have 1500 clients plugged into 50 switches
Click to expand...
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
Click to expand...
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


.
Click to expand...
Click to expand...


.
Click to expand...
 
B

Bernie Deitrick

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 said:
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

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

"[email protected]"
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


.
Click to expand...


.
Click to expand...
Click to expand...
 
Top