Sub Colour_groups()
'Andrew Cave andrew.cave.blogging at gmail 2014
'**colors a spreadsheet row according to whether the PREVIOUS cell in the activecell column
' matches it or not
'** if no match colors the cell to a different colour.
'**Gets width of colour block from rightmost cell in Row 1
Dim i As Long
Dim rg As Range
Dim c As Range, lastCell As Range
Dim rg2 As Range
Dim row As Long
Dim rowChange As Range
Dim groupCount As Long
Dim col As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim strActiveColumn As String
Dim intActiveColumn As Long
Dim darkColour As Long
Dim darkColour2 As Long
Dim lightColour As Long
Dim prevValue As Variant
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
'51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
'50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
'56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)
col = ws.Range(IIf(wb.FileFormat = 56, "iv1", "xfd1")).End(xlToLeft).Column 'rightmost cell on the sheet - move left to find edge of data
intActiveColumn = ActiveCell.Column
'build the column letter names up
i = Int(Log(intActiveColumn) / Log(26)) 'get log base 26 (so we know how long the column name will be
'get the string name of the column (A,B...AB...IV etc)
Do
If i > 0 Then
strActiveColumn = strActiveColumn & Chr(intActiveColumn \ (26 ^ i) + 64)
Else
strActiveColumn = strActiveColumn & Chr(intActiveColumn + 64)
End If
intActiveColumn = intActiveColumn Mod (26 ^ i)
i = i - 1
Loop While i >= 0
' reset values
i = 0
intActiveColumn = ActiveCell.Column
row = ws.Cells(IIf(wb.FileFormat = 56, 65536, 2 ^ 20), ActiveCell.Column).End(xlUp).row
row = ws.Cells(IIf(wb.FileFormat = 56, 65536, 2 ^ 20), ActiveCell.Column).End(xlUp).row
darkColour = 35
darkColour2 = 44
lightColour = 0
Set rg = ws.Range(strActiveColumn & "2:" & strActiveColumn & row)
Set rowChange = rg.Cells(1, 1)
For Each c In rg
If c.Value = rowChange.Value Then 'are we in a group?
groupCount = groupCount + 1 'count rows in group
Else
'and blank the current row
If i Mod 3 = 0 Then
Range(c.Offset(0 - groupCount, 1 - intActiveColumn), c.Offset(-1, col - intActiveColumn)).Interior.ColorIndex = darkColour
ElseIf i Mod 3 = 1 Then
Range(c.Offset(0 - groupCount, 1 - intActiveColumn), c.Offset(-1, col - intActiveColumn)).Interior.ColorIndex = darkColour2
Else
Range(c.Offset(0 - groupCount, 1 - intActiveColumn), c.Offset(-1, col - intActiveColumn)).Interior.ColorIndex = lightColour
End If
i = i + 1
groupCount = 1
Set rowChange = c ' keep a reference to the (potential) first row of the group
End If
Set lastCell = c
Next c
If groupCount > 1 Then 'handle the last row being part of a group
If i Mod 3 = 0 Then
Range(lastCell.Offset(1 - groupCount, 1 - intActiveColumn), lastCell.Offset(0, col - intActiveColumn)).Interior.ColorIndex = darkColour
ElseIf i Mod 3 = 1 Then
Range(lastCell.Offset(1 - groupCount, 1 - intActiveColumn), lastCell.Offset(0, col - intActiveColumn)).Interior.ColorIndex = darkColour2
Else
Range(lastCell.Offset(1 - groupCount, 1 - intActiveColumn), lastCell.Offset(0, col - intActiveColumn)).Interior.ColorIndex = lightColour
End If
Else 'colour the last row if it is not in a group
Range(Cells(lastCell.row, 1), Cells(lastCell.row, col)).Interior.ColorIndex = IIf(i Mod 3 = 0, darkColour, IIf(i Mod 3 = 1, darkColour2, lightColour))
End If
'handle the end of the lists -- ie if last one is a duplicate
Set ws = Nothing: Set wb = Nothing
End Sub
Chromatoma
Wednesday 16 April 2014
Excel - Colour duplicates
Doing data analysis, finding when you have repeats in a data-set is a really common task - particularly when you are exploring the data-set to get a sense for what might be there.
This macro takes the record block you currently have your active cell in, and then does alternate colours across the block each time it finds a change of data in the current column.
Here's an example to help you visualize.
This list is sorted in the right-hand column and unsorted in the left.
As the Active Cell is in the left column, the macro looks for changes in groups there. On each change of type, the colour changes.
Now that wasn't much use - but if we sort the "type" column and apply the macro:
You can see that the groups are now visually defined by the colours.
Wednesday 5 June 2013
Sybase - grant select to all tables in a schema
The problem is to grant a permission (select in this case) to a particular user, on all tables/views in a database.
Just copy and paste the results into a command line session.
select distinct 'grant select on ' + user_name + '.' + name + ' to username go' command from sysobjects
inner join SYSUSER on SYSUSER.user_id = sysobjects.uid
where
type in ('U' ,'V')
and
user_name='database_name'
Just copy and paste the results into a command line session.
Wednesday 20 February 2013
Calculate supernets to /16 in Oracle SQL
This bit of SQL calculates all the supernet addresses (to /16) for a given IP address (and mask) in Oracle.
Supernet addresses are the 'containing' IP Addresses based on powers of 2, so a /30 address covers 2(32-30) addresses (or 4 IPs).
A /26 address covers 2(32-26) addresses (64 IPs).
This works on Oracle 9, 10 and 11. If your version does not support
regexp_replace
there are some commented fields that utilise some instr
calls.
select
lvl supernet_mask,
ip,
regexp_substr(ip,'^\d+\.\d+\.') || resl2 || '.' || resl supernet
from
(
select
lvl, ip, mask, q3,q4,
case when lvl > 24 then
null
else
0
end supernet
,
floor(q4/(power(2,32-lvl))) * power(2,32-lvl) resl
,
CASE WHEN lvl <= 24 then
floor(q3/(power(2,24-lvl))) * power(2,24-lvl)
ELSE
Q3
END resl2
from
(
select lvl, ip, mask
, to_number(regexp_substr(ip,'\d+',1)) q1
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\2')) q2
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\3')) q3
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\4')) q4
/* -- if your version does not support regexp_replace
, to_number(substr(ip,1,instr(ip,'.',1,1)-1)) q1
, to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2 )-instr(ip,'.',1,1)-1 )) q2
, to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3 )-instr(ip,'.',1,2)-1 )) q3
, to_number(substr(ip,instr(ip,'.',1,3)+1)) q4
*/
from
(
select 33 - level lvl,'203.63.104.108' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.12' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.124' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.144' ip,30 mask from dual connect by level <= 17
) x
where mask >= lvl
) xx order by ip, lvl desc
) xxx
Sunday 3 February 2013
This Javascript function will filter a table according to a RegEx match with an input string. It does this by looping through each row of the table and hiding it if nothing matches.
This is a pretty useful bit of code when you are presented report outputs as html tables, your user likes to chop and change conditions, and the query itself is a little slow.
//takes the tableid, and the filterstring and loops through the table
//doing a regex match using the filterString. if line matches, the <tr> is shown
// but if no match anywhere on the line, it is hidden.
// Andrew Cave 04/02/2013
// andrew.cave.blogging@gmail.com
function filterTable(tableID,filterString){
t = document.getElementById(tableID);
if(typeof t=='undefined'){return;}//exit if the table hasn't been created yet
tbod = t.tBodies[0];
rows = tbod.rows;
rowct = rows.length;
filterString = trim(filterString);
filterString=(filterString.length==0)?'.*':filterString; //empty strings return all records
re = new RegExp(trim(filterString),"i");
for(i=0;i < rowct;i++){
cells=rows[i].cells;
cellct=cells.length;
hideme = 1; //start with hideme being true
for(j=0;j < cellct;j++){
var td = cells[j].innerHTML;
if(td.match(re)){ //checking for a match
hideme=0;
break;
}
}
if(hideme){
$(rows[i]).hide();
} else {
$(rows[i]).show();
}
}
}
Thursday 10 January 2013
List of contrasting web-colors
a simple list of web-colours for backgrounds, with a contrasting foreground colour (based on RGB luminosity values):
background-colr | color |
---|---|
#000000 | #FFFFFF |
#000080 | #FFFFFF |
#00008B | #FFFFFF |
#0000CD | #FFFFFF |
#0000FF | #FFFFFF |
#191970 | #FFFFFF |
#4B0082 | #FFFFFF |
#800000 | #FFFFFF |
#8B0000 | #FFFFFF |
#800080 | #FFFFFF |
#8B008B | #FFFFFF |
#006400 | #FFFFFF |
#9400D3 | #FFFFFF |
#2F4F4F | #FFFFFF |
#483D8B | #FFFFFF |
#008000 | #FFFFFF |
#B22222 | #FFFFFF |
#FF0000 | #FFFFFF |
#A52A2A | #FFFFFF |
#DC143C | #FFFFFF |
#8B4513 | #FFFFFF |
#C71585 | #FFFFFF |
#008080 | #FFFFFF |
#8A2BE2 | #FFFFFF |
#556B2F | #FFFFFF |
#228B22 | #FFFFFF |
#008B8B | #FFFFFF |
#9932CC | #FFFFFF |
#A0522D | #FFFFFF |
#FF1493 | #FFFFFF |
#FF00FF | #FFFFFF |
#2E8B57 | #FFFFFF |
#696969 | #FFFFFF |
#4169E1 | #FFFFFF |
#6A5ACD | #FFFFFF |
#808000 | #FFFFFF |
#FF4500 | #FFFFFF |
#4682B4 | #FFFFFF |
#6B8E23 | #FFFFFF |
#1E90FF | #FFFFFF |
#7B68EE | #FFFFFF |
#708090 | #FFFFFF |
#CD5C5C | #FFFFFF |
#808080 | #FFFFFF |
#D2691E | #FFFFFF |
#BA55D3 | #FFFFFF |
#20B2AA | #FFFFFF |
#778899 | #FFFFFF |
#9370D8 | #FFFFFF |
#B8860B | #FFFFFF |
#3CB371 | #000000 |
#5F9EA0 | #000000 |
#00BFFF | #000000 |
#32CD32 | #000000 |
#FF6347 | #000000 |
#6495ED | #000000 |
#00CED1 | #000000 |
#D87093 | #000000 |
#CD853F | #000000 |
#00FF00 | #000000 |
#DA70D6 | #000000 |
#BC8F8F | #000000 |
#FF69B4 | #000000 |
#FF8C00 | #000000 |
#FF7F50 | #000000 |
#F08080 | #000000 |
#FA8072 | #000000 |
#00FA9A | #000000 |
#00FF7F | #000000 |
#DAA520 | #000000 |
#48D1CC | #000000 |
#A9A9A9 | #000000 |
#8FBC8F | #000000 |
#66CDAA | #000000 |
#E9967A | #000000 |
#9ACD32 | #000000 |
#FFA500 | #000000 |
#EE82EE | #000000 |
#40E0D0 | #000000 |
#BDB76B | #000000 |
#00FFFF | #000000 |
#F4A460 | #000000 |
#FFA07A | #000000 |
#DDA0DD | #000000 |
#D2B48C | #000000 |
#7CFC00 | #000000 |
#87CEEB | #000000 |
#7FFF00 | #000000 |
#87CEFA | #000000 |
#DEB887 | #000000 |
#C0C0C0 | #000000 |
#B0C4DE | #000000 |
#90EE90 | #000000 |
#D8BFD8 | #000000 |
#FFD700 | #000000 |
#ADD8E6 | #000000 |
#FFB6C1 | #000000 |
#ADFF2F | #000000 |
#B0E0E6 | #000000 |
#98FB98 | #000000 |
#D3D3D3 | #000000 |
#7FFFD4 | #000000 |
#FFC0CB | #000000 |
#AFEEEE | #000000 |
#DCDCDC | #000000 |
#F0E68C | #000000 |
#F5DEB3 | #000000 |
#FFDAB9 | #000000 |
#FFDEAD | #000000 |
#EEE8AA | #000000 |
#FFFF00 | #000000 |
#FFE4B5 | #000000 |
#E6E6FA | #000000 |
#FFE4C4 | #000000 |
#FFE4E1 | #000000 |
#FAEBD7 | #000000 |
#FFEBCD | #000000 |
#FFEFD5 | #000000 |
#FAF0E6 | #000000 |
#F5F5DC | #000000 |
#FFF0F5 | #000000 |
#F5F5F5 | #000000 |
#FDF5E6 | #000000 |
#E0FFFF | #000000 |
#FAFAD2 | #000000 |
#F0F8FF | #000000 |
#FFF8DC | #000000 |
#FFFACD | #000000 |
#FFF5EE | #000000 |
#F8F8FF | #000000 |
#F0FFF0 | #000000 |
#FFFAF0 | #000000 |
#F0FFFF | #000000 |
#F5FFFA | #000000 |
#FFFFE0 | #000000 |
#FFFAFA | #000000 |
#FFFFF0 | #000000 |
#FFFFFF | #000000 |
Wednesday 29 August 2012
array of file handles in Perl
I used the following code today which creates, prints to and closes an array of file-handles using the IO::File core module.
I had over 100K of files in the same directory and had to process them (and send them into their own directory too, but that is trivial).
This works on Perl 5.14...
#==========================
# Andrew Cave
# 29 aug 2012
#
#==========================
use IO::File;
#create a blank hash of day values
my %month=(
'20120712' => '','20120713' => '','20120714' => '',
'20120715' => '','20120716' => '','20120717' => '',
'20120718' => '','20120719' => '','20120720' => '',
'20120721' => '','20120722' => '','20120723' => '',
'20120724' => '','20120725' => '','20120726' => '',
'20120727' => '','20120728' => '','20120729' => '',
'20120730' => '','20120731' => '','20120801' => '',
'20120802' => '','20120803' => '','20120804' => '',
'20120805' => '','20120806' => '','20120807' => '',
'20120808' => '','20120809' => '','20120810' => '',
'20120811' => '','20120812' => '','20120813' => '',
'20120814' => '','20120815' => '','20120816' => '',
'20120817' => '','20120818' => '','20120819' => '',
'20120820' => '','20120821' => '','20120822' => '',
'20120823' => '','20120824' => '','20120825' => '',
'20120826' => '','20120827' => '','20120828' => ''
);
my ($key,$val) ;
#open a filename based on the array values and
#put the file-handle reference in the hash, using the dayvalue
#as the key
while ( ($key,$val) = each(%month)) {
my $filename = "output_$key.dat";
my $fh = IO::File->new("> $filename")
or die "failed to create $filename $@\n$!\n";
$month{$key} = $fh;
};
open IN , '<', 'input.dat';
while(){
chomp;
my $line = $_;
if(/\d{8}/){# if the line has an eight digit number
my $day = $1;
my $fh = $month{$day}; # get the correct filehandle
print {$fh} "$line\n" ; #print to the filehandle - note the braces
#move the file into its own directory
#and add '.done' to the name
if(! -d "./$day/"){
system("mkdir ./$day/");
}
#print "$file|./$day/\n"; # the mv statements (if we want a record)
system("mv $file ./$day/$file.done");
}
}
close IN;
#close the array of files we created
# using IO::File
while (($key,$val)=each(%month)){
my $close_fh = $month{$key};
$close_fh->close();
}
Wednesday 28 March 2012
Create a Calendar listing in pure Oracle SQL
Just fiddling around...create a calendar for the next 1000 days starting with the 1st day of the current year. The calendar is the standard format putting Sundays in the left-most column a la :
2012 | APR | SUN | MON | TUE | WED | THU | FRI | SAT | 2012 | APR | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 2012 | APR | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 2012 | APR | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 2012 | APR | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 2012 | APR | 29 | 30 | 2012 | AUG | SUN | MON | TUE | WED | THU | FRI | SAT | 2012 | AUG | 1 | 2 | 3 | 4 | 2012 | AUG | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 2012 | AUG | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 2012 | AUG | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 2012 | AUG | 26 | 27 | 28 | 29 | 30 | 31 | 2012 | DEC | SUN | MON | TUE | WED | THU | FRI | SAT | 2012 | DEC | 1 | 2012 | DEC | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 2012 | DEC | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 2012 | DEC | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 2012 | DEC | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 2012 | DEC | 30 | 31 |
/*generate a calendar in Oracle using pure SQL
-- Andrew Cave andrew.cave.blogging at gmail-- 29/3/2012*/
with x as (select -- generate the days
trunc(trunc(sysdate,'YEAR')+level-1) date_ -- add days to 1st day of current year
from dual connect by level <= 1000 -- number of days to go forward
)
select yr,monthname,SUN,MON,TUE,WED,THU,FRI,SAT from
(
select yr
,monthname,MON_NUM,row_
,SUN,MON,TUE,WED,THU,FRI,SAT
from (
select -- display the dates
yr
,monthname,MON_NUM,row_
, to_char(max(decode(dayname,'SUN',dom,null))) SUN
, to_char(max(decode(dayname,'MON',dom,null))) MON
, to_char(max(decode(dayname,'TUE',dom,null))) TUE
, to_char(max(decode(dayname,'WED',dom,null))) WED
, to_char(max(decode(dayname,'THU',dom,null))) THU
, to_char(max(decode(dayname,'FRI',dom,null))) FRI
, to_char(max(decode(dayname,'SAT',dom,null))) SAT
from (
select
yr
,mon monthname
,dayname
,dom
,MON_NUM
,trunc((dom+ fdom-1)/7)-trunc(fdom/7) row_ -- what week of the month
,fdom
from
(
select date_ -- get date values for calculations and display
, to_char(date_,'DY') dayname
, to_char(date_,'YYYY') yr
, to_char(date_,'MON') MON
, to_char(date_,'MM') MON_NUM
, to_number(to_char(date_,'DD')) dom
, to_number(to_char(trunc(date_,'MM'),'D')) fdom -- day value of 1st day of month
from x
) x1
) x2 group by yr,monthname,row_,MON_NUM
) x3
union
select distinct -- this select does the day name column headers for the calendar
to_char(date_,'YYYY') yr
, to_char(date_,'MON') MONthname
, to_char(date_,'MM') MON_NUM
, -1 row_
,'SUN','MON','TUE','WED','THU','FRI','SAT'
from x
) x4 order by yr, MON_NUM, row_
Subscribe to:
Posts (Atom)