Have you ever had a need to filter some tables of data in Excel using masks? If so, this post may be useful to you. The principle is simple: place your data in one sheet (Data), create a mask on another sheet (Mask) consisting of cells filled with ones (where you want to keep the data) and zeros (where you want to remove the data), and finally, just multiply the cells of the previous two sheets in the third sheet (Filtered). A simple example is shown in the picture below.

However, the problem arises if your original data contains zeros, so in the destination sheet (Filtered) you won’t be able to decide which zeros are the “true” data and which are the result of masking. Since Excel doesn’t allow creating irregular selections, we are unable just to select all the zeros from the mask and paste it on top of our true data.

One way around this problem is as follows. Copy the whole mask into the destination data sheet right below the filtered data. While the whole mask is still selected, press Ctrl-F to invoke the “Find and Replace” window. In the search field write “0” and check “Match entire cell contents”. After pressing “Find all”, Excel will create a list of all cells (inside our selected mask) that have zero values at the bottom of the same “Find and replace” window. Now, leave this window opened and just copy the values of our filtered (or original) data on top of this mask (inside the “Filtered” sheet). After selecting all elements of the previously created list of cells inside the “Find and Replace” window, we will get the selection that we want to eliminate on top of our data.

This procedure was demonstrated on a very basic example. However, I found it very useful when the data tables become huge, which make manual selection of undesired cells extremely error prone. There is also a possibility to record a macro which would make this whole procedure automatic and bring it to a whole new level.

Zeljko Medenica

I was advised to use FolderShare application to share documents with my collaborators. But, in fact, we already have subversion for our code development. This automatically raised a question of why would we use FolderShare if we already have subversion installed on our server and in operation. It is not reasonable to compare subversion with FolderShare, because the purpose of these applications is very different, but it is possible to compare them for the task of collaboration on documents. So here we go:

Setup: there is an article me and my co-author want to write. Let’s call it OurArticle (it does not matter in what format the article is).

FolderShare:

1) I edit OurAricle and copy it into our shared folder (I can not edit it inside the shared folder, because it’s not clear what will happen if both of us edit it at the same time).

2) I email the co-author that changes are done

4) The co-author copies OurArticle somewhere else and edits it.

5) The co-author overwrites OurArticle in the shared folder.

6) The co-author emails me that I can work on the document.

Copying and overwriting is a clearly error-prone process. No version control, no automatic notifications, no error checking (FolderShare does not care if I overwrite the co-author’s change with mine, without even knowing I did it; FolderShare does not prevent me from editing document in the shared folder, saving it and then overwriting it with the old copy of the file that I was supposed to edit and then copy).

Subversion:

1) I commit my edits (this automatically sends notification to my co-author).

2) The co-author updates his copy (subversion will check if merging is required automatically).

3) The co-author commits his modifications (I get automatic email).

This is clearly a better solution. The most important is that subversion prevents us from loosing our modifications, by automatically checking for these modifications. We also get automatic version control for the article, in case we want to revive the paragraph we deleted a few editions ago.

FolderShare is suitable for sharing static data between computers, such as pictures, videos, etc. It’s not designed to handle collaboration. Subversion does require a server running with properly installed software, which is a hassle (luckily we already have it set up, so we get only benefits).

To top it off, subversion does not require a sync application constantly running in the background (better computer performance); subversion gives a third location for the backup; with subversion we have a web interface which allows us to get to the files without installing anything.

If we have to share some videos that do not require modifications, then FolderShare is a go. I also found out that there is a different alternative DropBox, which allows version control of the files, as well as the web access. It looks like if we would not have subversion already installed on our server, DropBox might have been a better solution for collaboration on the documents.

Any comments?

Alexander Shyrokov

If you have had the chance to use an iPhone or iPod Touch, you might have noticed a “small” inconvenience: these devices do not appear as removable storage drives in Windows, like prior versions of iPod (classic, nano, etc). This effectively prevents the iPhone and iPod Touch to be used for data transfer. There is some discussion online about why and how this happened. In my opinion, this is a two thumbs down situation for Apple for trying to limit the possible uses of these cool devices. I hope that this business model will soon be replaced by a more open-platform approach.

Fortunately, Albert Pelhe, a good friend of mine, informed me about a workaround to this problem. DigiDNA came up with DiskAid, an application that lets the user copy files to and from the iPhone and iPod Touch. It is freeware and claims to have 0% of adware, spyware and virus content. The only inconvenience is that it must be installed on every computer where the device is meant to be used as a removable disk drive.

If anyone from Apple reads this blog I have a request for them: please let information flow freely. You can try to control it, but you will become unpopular in the realm of techies. Thanks,

Oszkar

I just ran across Slideshare, a (beta) website that lets you share slides (yes the name is descriptive). I uploaded my first set of (PowerPoint) slides, and now I can embed them in this post - very cool:

Andrew Kun

Looking for a job? Check out this post for excellent suggestions on how to get on top of the resume pile.

Literature review is a big part of a scientific underatkening. Thanks to the Internet, most papers are available on line. This resulted (at least for me) in a big number of papers that I read and stored for feature references… Welcome to the age of data mining. As a researcher I have yet another problem on my hands: after I solved the problem of finding information, how do I keep track of it?

This subject was touched upon in other posts (bookmarking, mind maps, references). Given that my major focus of attention (at this moment) is my disertation, I want to be able to have a solution for referencing the information I have found. This basically has some sub-problems: creating references, searching through references, adding them to my documents (thesis, publications, reports), and creating list of references in my documents. I’ll skip discussion of why I was not satisfied with the above mentioned tools. Instead I’ll tell you what I see myself using: Zotero.

Long story short, this is a common scenario: I found a paper using Google Scholar (acm/ieee/etc). Two clicks and my reference is created (with all the author, title, abstract, etc. information added properly). Another click and I’m actually reading the paper. One last click and the pdf is stored with my reference (for feature review). If I found something interesting I can add notes. I can tag the reference (for feature searches). I also can link other references as related. The bottom line: creation of references is a smooth, easy and quick process.

There are plugins for OpenOffice.org Writer and Microsoft Word that allows one to use Zotero reference in the documents. The process of adding references to the text and creating refere lists is a two click deal (also smooth and quick). Installation of both plugins (for firefox and for Writer) was very simple.

For more information take a Zotero tour.

Alexander Shyrokov.

As many of you may know, I spent a good part of the summer in front of the video-editing computer transcribing data from videos of experiments. I used Adobe Premiere to organize and view the videos. However, Adobe Premiere was made for movie/cinematic editing, not data transcription, so I had to “invent” a few ways to import and export the data. I also found a few problems/bugs, so I wrote all of this up in a document which can be found here. I structured this as a sort of FAQ on using Premiere, so I also added some basic (yet vital) processes that took me a while to figure out (like capturing video and exporting videos). Enjoy!

Owen Derby

I have recently realized that when I open multiple documents in Excel they all show up separately on Taskbar but show up in the same window of Excel. This can be very aggravating for anyone who works with multiple documents and multiple monitors. After becoming fed up with Excel I did some research online and found a solution.

My Computer
Tools
Folder Options
File Types
Choose XLSX (XLSX for 2007 or XLS for 2003)
Go to Advanced

Uncheck “browse in same window” in advanced window.

Then highlight Open
Edit

Make sure in the Action box it says &Open

Copy one of the following and paste into “application used to perform action” (Check whether current path has OFFICE11 or OFFICE12 then copy and paste one of the following:

“C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” “%1″

“C:\Program Files\Microsoft Office\OFFICE12\EXCEL.EXE” “%1″

Check the box next to use DDE

Remove anything that is in DDE Message box and DDE Application Not Running box.

Make sure the application box says: EXCEL

And in the Topic box it says: System

http://techrepublic.com.com/5208-6230-0.html?forumID=101&threadID=234896&messageID=2510797

Thanks Jon.

Jonathan Oppelaar

Check out www.tinyurl.com. It allows you to create tiny URLs out of long and winding ones. This is neat when you want to send a link in an email to someone without having to worry if their email client will interpret the multiple lines of the link correctly.

It’s also neat if you want to include links in presentations or posters. I’m preparing a presentation for an upcoming talk at Microsoft Research and have just used this site to create a couple of links: one to this YouTube video of the Project54 driving simulator and another to this eceblogger post about the eye tracker that we’ve recently installed in the simulator. Best of all, tinyurl allows you to create your own custom tiny URLs. So, I’ve created www.tinyurl.com/p54sim and www.tinyurl.com/eyetracker.

Andrew Kun

Hello,

AutoHotKey is a handy utility that can be used for automation tasks. This program can move the mouse or send key events to different applications. It also can show dialogs. I mostly use it as a macro recorder that works across all the applications. It supports a simple (as in easy to learn), but powerful scripting language. You can learn more about it by watching this video:

Blue skies!

Alexander Shyrokov