Linking 2 spreadsheets based on look up value

I had two spreadsheets, one with email address and a couple of values. The other had email address and several more values. I wanted to link the two spreadsheets based on the unique email address, and copy items from one table to the other. I used Mac Numbers and entered the tables as two sheets. I used the Lookup function. In one table I created a new column. In that column cell I entered “=Lookup”, then clicked on the email address in that table, then clicked on the column in the second table where email addresses were located, then clicked on the column where zip codes were located (or other values I wanted to transfer). Then hit return and the values were transferred.

Lookup – Search for, Search where, Results.

Gene Portuesi’s Cyclopedia

I don’t remember how I developed an interest in cycling back in the early 1970’s. At one point I lived in East Lansing, Michigan and worked in nearby Lansing. Without a car and with intermittent bus service I needed a reliable way to get to work. I bought a Schwinn Varsity back when Schwinn made decent bikes. That bike was stolen but I enjoyed riding enough that I searched for a new bike. I came across a reference to a bike shop catalog published by Gene Portuesi called Cyclopedia. I pored over the drawings of bicycle parts and the articles about all things bike. I vaguely remember traveling to his shop and buying imported bikes for my wife and me.

Today I found copies of the 1974 Cyclopedia catalog that I owned on Sheldon Brown’s site. I think Gene was an early importer of quality European bikes but there is not much info on the web that I’ve found other than a few references to his bike shop in Detroit and later Caddilac, Michigan. Maybe it’s time for a wikipedia entry…

Counting text occurrences in Numbers

I wanted to summarize the data in a spreadsheet of information about use of Capital Bikeshare bikes. The data contain start and end station and several other variables. I just wanted to count the number of times a bike was checked out from a station in Fairfax County. The data are located on the Capital Bikeshare site. Currently the data are stored in zip files by month.

The data were downloaded, unzipped, and the csv file was located into Mac Numbers. Stations in Fairfax County have station numbers from 32200 to 32236. I sorted by station number, then cut an pasted this range into a new sheet. At the bottom of the data I added cells that contained just the station name. Then I used this formula in the adjacent column to count the occurrence of each station name in the data above:

=COUNTIF($E$2:$E$624,A627)

Where $E$2:$E$624 is the absolute cell reference for the range of station names. A627 is the station name, a relative reference so that when the formula is copied and pasted to the next row, the next station name will be compared and counted.

Merging images with GIMP

Open GIMP and open the first image. Open another image either with Preview or GIMP, select and copy the image, then paste into GIMP. In the Layers menu the pasted image will appear as a Pasted Layer. Select that layer and click on Create a new layer in the lower left of the window (looks like a blank page with a plus sign in the upper left). The pasted layer should now be selected. Move the layer to the new position. It won’t appear beyond the limit of the old image unless it’s created as a new layer. Then under the Image menu select “Fit canvas to layers” and the second image should now appear in a new, larger canvas. Repeat for additional images.

Google Sheets duplicate rows

If a Google Sheets spreadsheet has rows with duplicates, there’s a way to highlight where the duplicates are located, and then manually delete them: From LifeWire:

Open the spreadsheet you want to analyze in Google Sheets.

Highlight the column you want to search through.

Click Format > Conditional Formatting. The Conditional Formatting menu opens on the right.

Confirm the cell range is what you selected in Step 2.

In the Format cells if… drop-down, select Custom formula is. A new field appears below it.

Enter the following formula in the new field, adjusting the letters for the column range you selected:

=countif(A:A,A1)>1
In the Formatting style section, choose a fill color for the duplicate cells. In this example, we’ve chosen red. Then click Done.

Enlarging the canvas using Preview

I wanted to create an image containing two other images of about the same size. Preview doesn’t have a tool for enlarging the canvas. I found this workaround on apple.stackexchange:

  1. Select all (cmd+A) and cut (cmd+X) the image from its canvas (convert to png if asked). 
  2. Resize the image as desired from the “Tools” menu (unlock the proportions if desired)
  3. Re-paste original image from the clipboard (cmd+V) and move it where you want it on the resized canvas
  4. Use cmd+- if necessary to zoom out (you’ll see checkerboard where the canvas is and white beyond)
  5. Paste in other images, etc, as you like

Using Regular Expressions in Search and Replace

Using Google docs, regular expressions can be used to conduct search and replace operations. I had a list of email addresses that were preceded by the address in quotes. I wanted to remove the quotes and everything between them: “Name5@something.com”

This expression found everything up to the @ sign: [“][a-zA-z0-9.]+ (I assume A-z should be A-Z)

This expression found everything: [“][a-zA-z0-9.]+[@][a-zA-z0-9.]+[“]

The replacement was null. Regex cannot be used for replacement expressions in google docs. However, BBedit does allow regex for replacement.

In this example I wanted to capitalize the first letter of each sentence in a text file. I use \n to find the new line before the next sentence. I use and then used parens to save what was found, and [a-z] to find all lower case letters that come after a new line:

\n([a-z])

The lower case letter after the new line found with the above expression was saved in the expression \1. To make that lower case letter upper case I used \U before \1

\n\U\1

(this info was found in a BBEdit tutorial)

From the Google help file for regular expressions:

ExpressionDescriptionExampleMatchesDoes not match
.A period represents any character in the given position.d.do, dog, dg, adsfog, jog
*An asterisk after a character represents a search for that preceding character repeated 0 or more times.do*gdog, dg, dooogdOg, doug
+A plus after a character represents a search for that character displayed 1 or more times.do+gdog, dooogdg, dOg, doug
?The previous expression is optional.do?gdg, dogdOg, doug
^A caret must be placed at the beginning of a regular expression. It signifies the string starts with the character(s) or sequence placed after the caret. Note: This regular expression only works with Google Sheets.^[dh]ogdog, hogA dog, his hog
$A dollar sign must be placed at the end of a regular expression and signifies that the string ends with the character(s) or sequence placed before the dollar sign.Note: This regular expression only works with Google Sheets.[dh]og$dog, hog, hot dogdogs, hogs, doggy
{A, B}The previous expression is repeated between A and B times, where A and B are numbers.d(o{1,2})gdog, doogdg, dooog, dOg
[x], [xa], [xa5]A character set indicates that just one of the given character(s) should occur in the current position. Usually, any characters are valid within brackets, including characters mentioned previously in expressions: [xa,$5Gg.]d[ou]gdog, dugdg, dOg, dooog
[a-z]A character set range signifies a search for a character within the given range of characters. Common ranges include a-z, A-Z, and 0-9. Ranges can be combined into a single range: [a-zA-Z0-9]. Ranges can also be combined with character sets (mentioned previously): [a-zA-Z,&*].d[o-u]gdog, dug, dpg, drgdg, dOg, dag
[^a-fDEF]A character set beginning with a ^ signifies a search for a character that is not within the given set.d[^aeu]gdog, dOg, dig, d$gdg, dag, deg, dug
\sAny white-space character.d\sgd g, d[TAB]gdg, dog,