technology

LibreOffice Calc – Comparing and Aligning Two Lists Using VLOOKUP

One of the tasks I have to do regularly as part of my job is to compare two lists to see which items are missing on one list but not the other. I have been doing this by hand but figured there had to be a way to do this in Excel. I finally figured it out but don’t want to forget how to do it. So, I’m documenting it here so I can draw on this whenever I need to do it again.

Here’s the scenario. I have a list of “items” but every month or so I receive an updated list of “items” from someone else. During that month, some of the items on my list have been taken off and some have been added to it. Likewise, the same has happened to the other list that this other person sends me (I’m being really vague here because I work at a university and there are laws that govern academic records).

My list and the other list have to be kept synchronized but we have two separate databases that are used for this because… (argh, yeah). Anyway, what that means is that I have to compare the two lists and quickly find the items on one list but not the other and vice versa. Here’s how to do it quickly in LibreOffice Calc.

Here are two lists:

(NOTE: You can actually do this in two separate Calc sheets or within the same one.)

LIST 1 is in Column A and List 2 is in Column C.

In Column B I am going to create a function that allows me to search all of Column C to see which of the items in LIST 1 show up in LIST 2. Click in Row 2 of Column B then click on the Function Wizard (or start typing):

In the Function Wizard dialogue box, you’ll see this:

Search for VLOOKUP then double-click it and you’ll see this:

You now need to build the function. This is where I got confused, so I’m going to try to explain this carefully.

The first box is the “Search criterion.” Basically, this is what you want to find. In our example, let’s say that we want to see which items in List 1 are in List 2 (Hint: It’s apples and grapes.). So, we are going to put in the Search criterion that we want the software to search the items in column A or LIST 1. We do this by simply selecting cell A2 (you can do this by typing it in or by selecting it by clicking the button to the right of the box and then selecting A2 like I did below):

Next is the “Array” box. This is the content through which you want the software to search to find the items in LIST 1. Again, you can type this in or select it using the button to the right. However, there is an important change that you need to make here. If you select the array with your mouse and leave it as is, the values will change as you drag this formula down in Column B as it will assume you want to adjust the array as well. Since we don’t want to adjust the array but rather want to search through the same items in LIST 2, we need to put dollar signs before the letters and numbers in the Array which will lock the boundaries of the array into place so they don’t change when we copy the formula to other cells, as shown below:

The next part is the part that threw me off for a long time in figuring this out. The “Index” is the column in the Array you want to compare to the Search Criterion. In this case, all you need to do is specify “1” since there is only one column. But, presumably, you could have an array made up of multiple columns and want to choose just the 4th column (so you would enter 4 in the Index). Here’s how this looks:

Finally, the last box that is part of our function is the “Sort order” box. This tells the software whether the lists are ordered alphabetically or not. If they are not, enter zero (“0”). If they are, enter “1.” If you leave this blank, LibreOffice Calc thinks the lists are ordered. So, make sure you fill this out.

Once you’ve got that all entered, your formula should look like what I have above. Hit “OK” and it will search the Array C2:C7 for “bananas.” If it finds it, it will return “bananas.” If it doesn’t, it will return “#N/A” as shown below:

You can then, of course, drag this formula down. When it finds the item from LIST 1 in LIST 2 it returns that item. When it doesn’t, you get #N/A.

And there you have it. A LibreOffice Calc function for searching a list for a target and returning an indicator.

(NOTE: What I typically do after I have run this function is sort by the column where the function is located so I know which items are missing from LIST 1 and which are missing from LIST 2.)

technology

Linux: Getting “Find” working in Dolphin on KDE (Linux Mint and Kubuntu)

One of the reasons I switched to KDE from Gnome was Dolphin, the file manager that ships with KDE.  When I made the switch a couple of years ago, the Find feature in KDE worked really well.  But some time in the last couple of years, the two distributions I’ve been using – Kubuntu and Linux Mint KDE – haven’t had the Find feature working from the base install.  I’ve muddled along without that feature for about two years (I don’t always need it, but there have been a few times when I really did need it and it didn’t work).  I finally figured out how to get it working.  It has to be one of the most ridiculously broken elements of Linux I’ve ever discovered as the solution is convoluted and counter-intuitive.

To begin with, from the base install in Dolphin, here is the Find button:

enabling find function in Dolphin in KDE

If you click it, it will open a find dialogue in the location bar at the top of Dolphin:

enabling find function in Dolphin in KDE
(click for full size)

If you try to find something, you’ll get an error message that says, “Invalid protocol” that looks like this:

enabling find function in Dolphin in KDE
(click for full size)

Dolphin has done that for the last two or three years or so, which means I haven’t been able to use this very basic feature of the file manager.

If you look around for advice on how to fix this, you’ll get mired in a bunch of forums that suggest different things about “baloo,” the new search program in KDE (that replaced Nepomuk, the failed, processor-hungry semantic search engine that no one really liked).  Here’s the problem with “baloo”: it’s not installed by default in Linux Mint KDE or Kubuntu.  That’s actually fine if you don’t need this search feature.  But, and here’s the convoluted part of this, you don’t actually use baloo for the search function in Dolphin.  However, you have to install it in order to enable the search function in Dolphin to work, but then turn baloo off.  Seriously!  It’s rather absurd and broken at the moment.

Here’s what you have to do.  First, install baloo4 from synaptic:

enabling find function in Dolphin in KDE
(click for full size)

If you try the search function now, it still won’t work.  Dolphin won’t give you the error message anymore, but it also won’t find anything.  It just gives you an empty page of results, regardless of what you search for.  But, installing baloo does something that makes enabling the Find feature possible.  If you open up System Settings, you’ll see a new icon that wasn’t there before – Desktop Search:

enabling find function in Dolphin in KDE
(click for full size)

We’ll return to that System Setting option in a minute.

Next, go back to Synaptic and install the following packages: kde-baseapps, systemsettings (probably already installed), and kfind (also probably already installed).

enabling find function in Dolphin in KDE
(click for full size)

You can still try searching in Dolphin after you’ve done this, but it won’t work.  There is one more completely counter-intuitive step.  Once you’ve installed kde-baseapps (and the other two packages), go back to the System Settings window and click on the new Desktop Search icon.  There is a check box below the window where you can exclude locations that says “Enable Desktop Search.”  Uncheck it and click “Apply”:

enabling find function in Dolphin in KDE
(click for full size)

Now, try searching in Dolphin and, voila, it works:

enabling find function in Dolphin in KDE
(click for full size)

This fix for the Find feature in a basic program in KDE is completely counter-intuitive.  In sum, in order to turn on the “search” feature, you have to install a package that you aren’t going to use, install another package that you are going to use, and then turn off the first package (baloo).  Why?  Why?  Why?

KDE programmers – I love your software!  I really, really, do.  But this makes no sense.  Can you please decide on a file/folder search solution, install it by default, and then make it a simple click of a button to turn it on or off?  This should not be anywhere close to this complicated!