On Tue, 22 Feb 2005 10:14:00 -0000, "Bob Phillips"
wrote:
"JC" wrote in message
...
I don't understand the logic in the Sub SortData. I will be near a bookstore
tomorrow morning and will look for a book on Excel macros and try to
figure out what you are doing.
The trick in this one is not really in the macro. All the macro does is
create 2 helper columns where you created 8, to get an IP address that can
be sorted. In your spreadsheet, if say the IP address was 1.2.3.4, you ended
up with 1002 in H, and 3004 in I, and presumably you sort on those 2
columns. I end up with 1002003004 in one column and sort on that. So all the
macro does is insert a formula in H (That isoltaes the IP address from
column C) and in I (which makes a number from the IP address), and copy down
to all rows, then sorts on column I. The macro is very simple, it sets the
formulae and sorts the data.
I haven't had time to test this code out yet. I've grabbed a couple of books
on Excel VBA coding and will settle down with them for a while to get an
understanding on macros.
In the mean time, I will make do with the ColourData macro code.
I have coded in Fortran, Basic and QuickBasic before so it shouldn't be
too steep a learning curve.
As I said, I think the code is so simple you should be able to understand it
now. The trick is in the formula that I create, which, with all respect,
unless you are into complex worksheet formulae, you are not going to
understand. Just try on a copy workbook and see if it works, if it does, lie
back and enjoy :-)
I would still like to know the rules for colouring the data, to automate
that. :-)
Bob
The process is simple and difficult at the same time. The simple part is to
look for multiple entries with the same source IP address. What makes it more
difficult is that the end result I want is really based on ISP address ranges.
ISPs have large address ranges. For example ISP X may have an IP address range
from 1.2.3.4 - 1.2.32.255. A complication is that ISP X may have multiple IP
address ranges such as 1.2.3.4 - 1.2.32.255 and 12.16.0.0 - 12.18.255.255.
It would be easy to program a macro to colour multiple entries from a single IP
address but not so easy to cater for ISP address ranges. It requires knowledge
of the entire range of IP address allocations probably in some form of lookup
table and an updating process to keep it current. I check with APNIC etc when
the number of entries exceeds 3 to get the ISP details including address range.
This I put into column G and may result in me uncovering more entries coming
from that ISP's address range. I have listed the most common offending ISPs in
a workbook but this is only a tiny proportion of the entire list.
I send out emails to the ISP if more than 3 entries occur in the month. If
more entries appear on following days I send out additional emails listing all
entries to date this month to reinforce my original request that the spam etc
being sent be stopped. Detecting these additional entries would be more
difficult if the macro automated the colouring process.
This process results in about 20% of the entries being coloured with the rest
remaining grey. I realise that the system I am using is inefficient and does
not detect all cases of multiple entries coming from an ISPs address range.
I hope this helps you understand the process. If you would like to see a copy
of the current spreadsheet I could email it to you.
--
Cheers . . . JC
|