PDA

View Full Version : VBA or In-Cell functions for Duplicate handling?



JP2R
04-17-2008, 05:31 PM
Background:
We work on a secure campus and have recently started to search for ad hoc networks (rogue? networks) on the campus.

I have created a spreadsheet that has:

Column A: MAC_Address
Column B: Name
Column C: Email
Column D: SSID
Column E: Host_Name
Column F: Email_Sent (Y/N)
Column G: Date_Sent
Column H: First_Time (Detected)
Column I: Second_Time
Column J: Third_Time
Column K: Resolved

1. MAC_Address is what I was originally thinking to be unique, however, I noticed that I could detect an ad hoc from a certain MAC Address with a particular SSID and send them and email and ask them to remove it, but they wouldn't check to see if they had any others 'up', so when I ran the search later I would find them again with a different SSID (I hope this makes sense).

So I was trying to look and check for duplicate MAC Addresses, and then if it was duplicated to insert a row, just below the first event/entry so that that MAC Address could be inserted with the new information...

I wasn't sure if this could be done with an in-cell function or VBA - I was trying to figure out how best to update the information, but I've been thinking about it so long I thought I had best ask you guys...

I can do all sorts of in-cell stuff, but am really weak on VBA...
I made a post earlier asking about how to get the data from the Cisco website into Excel so that I could update, but wasn't sure how to do this...

I have to automate as much as possible because I don't want to spend all my life on this project - if it can be automated - anyone can do it.

If you need a visual I can try and put something unclassified together...

I am using a Dell Laptop//Microsoft Office 2007 Prof//Browsers used IE and Firefox most current versions

-- Jae

Bob Phillips
04-17-2008, 06:47 PM
Best to post an example, before and after. It seems doable quite simply, but it is a tad difficult to envisage the data.

JP2R
04-17-2008, 07:09 PM
Best to post an example, before and after. It seems doable quite simply, but it is a tad difficult to envisage the data.
Okay - sure thing.
I've changed the MAC Addresses and the names of the people and other data.
The file is in Excel 2003 (.xls).

I did not provide a duplicate - but - for example - if MAC Address for "Harry Who" showed up on the list again - when the report was checked - a row would need to be inserted directly below the first event of his address...

after 'talking' with Lucas - he was kind enough to provide a way to gather the data from a table in a webpage - I am hoping to make that work to use that information - compare it to the table in Excel - and where there is a duplicate MAC - create a row and post it there...

Let me know if I need to make it more clear - I keep trying it by doing functions - or trying to do some sort of record - but I'm not having much luck just yet...

Let me know if it didn't get attached.
-- Jae

JP2R
04-17-2008, 07:15 PM
I apologize, I forgot the after file (still learning how to use this forum)

Sincerely,

-- Jae

JP2R
04-17-2008, 07:44 PM
I was thinking to use a VLOOKUP - which was my first thought - but I was having trouble listing the order in which events would occur - the toughest part is the visualization...

So...

1. Retrieve the data from the HTML page
Q. Where to put the data? In a separate page (like a holding place?)

2. Compare that data retrieved to the existing table for duplicates
a. If a duplicate MAC address is located
1) Insert a new row below the most current entry - copy and paste that data in that row
b. If no duplicate located take the data and add it to the most bottom row.

Now I have seen a script in the forum that can tell where the last row is in a spreadsheet (actually have seen more than one - not sure which is best)...and I know how to use the VLOOKUP as a function outside of VBA, but don't know much more...

any help and direction would be awesome...
I'm trying stuff on my own - but nothing that I have figured out that really works (yet)...

Headre
04-18-2008, 09:17 AM
Not an expert but this should work. Assuming your putting your data on sheet wwism1


Sub InsertOffender()
'
' InsertOffender Macro
' Macro recorded 4/18/2008
'
' Keyboard Shortcut: Ctrl+k
'
Dim intNumRows_AdHoc As Integer
Dim strNewMacAddress As String
Dim strSSID As String
Dim myRange As Range

'find out how many rows in Ad_Hoc sheet
intNumRows_AdHoc = NumRows("Ad_Hoc")
For a = 2 To NumRows("wwism1") - 1
strNewMacAddress = Worksheets("wwism1").Range("A" & a).Value
strSSID = Worksheets("wwism1").Range("B" & a).Value
intNumRows_AdHoc = NumRows("Ad_Hoc")
Set myRange = Worksheets("Ad_Hoc").Range("A1:L" & intNumRows_AdHoc).Find(strNewMacAddress, , xlValues)
If Not myRange Is Nothing Then
Rows(myRange.Row + 1).Select
Selection.Insert xlShiftDown
strrow = myRange.Row + 1
Range("A" & strrow).Select
ActiveCell.Value = strNewMacAddress
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D" & strrow).Select
ActiveCell.Value = strSSID
Else
Range("A" & intNumRows_AdHoc).Select
ActiveCell.Value = strNewMacAddress
Range("D" & intNumRows_AdHoc).Select
ActiveCell.Value = strSSID
End If
Next a
End Sub
Private Function NumRows(strSheet As String) As Integer
Dim intNumRows As Integer
intNumRows = 1
Do Until Worksheets(strSheet).Range("A" & intNumRows) = ""
intNumRows = intNumRows + 1
Loop
NumRows = intNumRows
End Function

JP2R
04-18-2008, 09:55 AM
Awesome!
I'm going to take a look at this and yes, I was putting the data in wwism1!

I've been messing with this all day - can't wait 'til I get to the point where I don't have to chop up little functions to get things done...

Practice - Practice - Practice

and much help and patience from people here in the forum -
Thank you so much!

-- Jae

JP2R
04-18-2008, 11:03 AM
Thank you for all your assistance - this worked perfectly!
I'm going to have to really break this down for my brain to comprehend - I'm hoping they don't consider adding anymore columns...

This is great!
With much respect!

-- Jae

Headre
04-18-2008, 11:26 AM
Your very welcome. Was good practise. I'm sure there are better ways to get it done but as long as it works. If they do add more columns no problem. I get the data from the A & B columns on your wwism1 sheet and add it to the A & D columns on the Ad_Hoc sheet. Either at the end or in the line that gets inserted if the mac address is a duplicate.

strNewMacAddress = Worksheets("wwism1").Range("A" & a).Value
strSSID = Worksheets("wwism1").Range("B" & a).Value

Range("A" & intNumRows_AdHoc).Select 'Selects the cell
ActiveCell.Value = strNewMacAddress 'Pastes the data

the rest is really only checking for the data and inserting a row if it needs to be....