Category: Knowledge

Spreadsheet highlight common cell values between two columns

Scenario

Suppose we are having two columns in a spreadsheet and we are trying to find the common values between them. That is highlight column values if they are present in another column.

For example lets say we have some ids in two different columns. Both columns have some data in common and some data exclusive to each other. But our focus here is to highlight the values in second column if they are present in first column and vice-versa.

To do this we are going to use conditional formatting, as the name suggests it will format the cells based on a condition. Here we are going to specify our own condition.

Look at the image below

spreadsheet with two columns with values/ids

Here we want to highlight the values in column ‘D’ if they are present in column ‘A’.

So to do that, first select the column i.e column ‘D’ and then go to conditional formatting. Go to Format->Conditional -> Condition->Formula (choose from dropdown) in the input box enter the formula COUNTIF($A:$A, $D1) like in the image below.

conditional formatting applied on 3rd column

After you click ‘OK’ you can see that 2nd column values that are present in column 1 will be highlighted like below.

image showing values in 2nd column being highlighted

Now to do the reverse i.e to highlight values in first column from 3rd column we need to do apply similar formula COUNTIF($D:$D, $A1) on column ‘A’.

Note
  1. You can remove conditional formatting rule that has been applied
  2. For this formulae to apply there should be common values between these columns.
  3. We can choose custom formatting like different color, size etc for conditional formatting.

You can also watch video to see how it is done in Google Spreadsheet

Escape HTML entities in JavaScript variables to render as plain text

Rendering strings containing HTML entities would require special attention as browser can only understand HTML tags and are formatted according to the HTML entities. So this post will help in dealing with such cases which will render HTML entities as plain text in browser.

First step:

1
2
3
4
5		 function escapeHtml (string) {
return String(string).replace(/[&<>”‘`=/]/g, function (s) {
return entityMap[s];
});
}

This is the major function that does the espcaping of HTML entities. As you can see it require an entityMap which contains all HTML entities that you want to require.

So here is our enitityMap:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
var entityMap = {
        '&': '&amp;',
        '<': '&lt;',
        '>': '&gt;',
        '"': '&quot;',
        "'": '&#39;',
        '/': '&#x2F;',
        '`': '&#x60;',
        '=': '&#x3D;'
};

As you can notice that these are the possible basic HTML entities, you can convert them to escape string that browser understands as plain text.

So what next:

We are all ready and I am giving you a sample text to test this.

1
2
 
text = escapeHtml(text);
$("#demo").html(text);

When you do all the above mentioned steps correctly, you would get the following result.

1
 <स्क्रिप्ट भाषा = PHP> … </ script> </ Body> # यह एक टिप्पणी है, और print <<<END $ 4 <tab> = <tab2 <tab> + <tab> 2// रिक्त स्थान और टैब <? Php ?>

Observe that all HTML tags are restored as it is.

Still having a problem you can check our live demo over here.

Downloading A Zip File Using Javascript Is Not Rocket Science! Know how!

Initially, the prerequisite for downloading a simple text file in javascript requires FileSaver.js. This can be downloaded from here.

Next step would be the creation of zip file.

1. Download jSZip and load it in your html.

2. Here comes the core part where we initalize jSZip and download a zip file.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
 var zip = new JSZip();         

    //skip this step if you don't want your files in a folder.
    var folder = zip.folder("example");
    folder.file("myfile1.txt", "HELLO WORLD IN 1ST FILE"); //requires filesaver.js
    folder.file("myfile2.txt", "HELLO WORLD IN 2ND FILE");

    //...so on until you have completed adding files

    zip.generateAsync({type:"blob"})
               .then(function(content) {
                //see FileSaver.js
                saveAs(content, "example.zip");
      });

A simple perl script to make a file content unique

This simple perl script does the job of sort -u (of course not in windows)
 
Script:
 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
#!/usr/bin/perl

use strict;

use warnings;


#File reading code

open(my $in,"<:utf8",$ARGV[0]) or die "Cannot open $ARGV[0]:$!n";

my @in =<$in>;

close($in);


#declare hash

my %hash=();


#store content in hash

foreach my $in (@in) {

 chomp($in);

 $hash{$in} = 1;

}


#Finally print the hash

foreach my $keys(sort keys %hash){

 print "$keysn";

}
How to run:
 
Lets assume that our script is saved as my_unique.pl 

$ perl my_unique.pl <filename>
 
* Without angular brackets.
 
Explanation:
Here in Perl hash the duplicate keys are overridden which means only single instance of each line in the file is stored in the hash. Remember that while storing the lines in hash each single line is treated as key so as to remove duplicates. 
 
Note: Always use strict and use warnings when dealing with Perl code so as to write bug free code.

Linux: How to find duplicate lines count in a file from terminal.

Linux has many commands that are useful to process/analyze a file. In this post I would just explain a simple utility that would just print out the number of times each line is repeated in that file.

So here is the command:

terminal$ sort yourfilename.txt | uniq -c

Here yourfilename.txt can be any file name which I used here for an example.
Suppose the contents of yourfilename.txt be

line1
line1
line2
line3
line1
line3

Output:

3 line1
2 line3
1 line2

Explanation:

The sort command is quite self explanatory over here its output is piped/redirected to uniq. Uniq command requires its input to  be sorted(keep in mind always hard to remember). Uniq -c just prints the count of each line.