Twitter Digest

More Favorite Excel Formulas

Making Headings

Quite often I would have columns of headings but when I went to export the headings, I would only want the first instance to appear in the text I was using. For example if I was doing a list of recipes and they were divided up into Chicken, Beef and Seafood, I would like the heading “Chicken” to appear only before the first entry—think Table of Contents.

Thus:

Chicken Recipe 1
Chicken Recipe 2
Chicken Recipe 3
Beef Recipe 1
Beef Recipe 2
Seafood Recipe 1
Seafood Recipe 2
Seafood Recipe 3

 

Should be:

Chicken Recipe 1
Recipe 2
Recipe 3
Beef Recipe 1
Recipe 2
Seafood Recipe 1
Recipe 2
Recipe 3

Again the first thing you ned to do is visualize the problem and then express it logically. If the heading is the same as the heading above then don’t show it. If it is not the same then do show it. I also threw in a bit to accommodate the space between Beef and Seafood in case it was necessary.

The first thing you need to do is insert a new column between the headings (column A) and the recipes (column B). Actually you can make the new column for the calculation anywhere you want but it is much easier to keep alongside the original.

A B C
Chicken Recipe 1
Chicken Recipe 2
Chicken Recipe 3
Beef Recipe 1
Beef Recipe 2
Seafood Recipe 1
Seafood Recipe 2
Seafood Recipe 3

Then you fill column B with the following formula:

=IF(A2=””,””,(IF(A2=A1,””,A2)))

This is constructed from basic if statements. You will use them a lot if you spend any amount of time working with formulas and calculations. The syntax for an if statement is  IF(logical test, is true then do this…, else do this…) So this =IF(a=b,”A”,”B”) simply put, is  read as if a is equal to b then write A, if not then write B. My formula above uses two nested if statements, one that checks to see if the current row is blank or not and one that determines whether it is the same as the row above.

Is the row empty? If so then leave it blank; if not then go on to the rest of the calculation. Empty (blank) is indicated by two quotes with nothing (not even a space) between them.

=IF(A2=””, “”, next if statement)

The next if statement then decided if the current cell matches the one above or not. If it does, if leaves it blank. If it doesn’t then it put in the text from the current cell.

=IF(A2=A1,””,A2)

All together:

=IF(A2=””,””,(IF(A2=A1,””,A2)))

If cell A2 is blank then leave blank space, if not then see if A2 is equal to the cell above (A1). If it is, then again leave a blank space. If not, then write in the contents of A2.

One thing to remember about this sort of thing is the calculations are all related to the original list. If you delete column A—which you will likely have to if you are going to export it to a text file—then the text in your column B will also disappear. The easiest away around this is, just before you delete the column, you copy column C and then paste it right back using Edit: Paste Special, then select Text. This will paste not the column of formulae you cut, but the results in text form—perfect for exporting!

But I need to get rid of Headers

Just as often I would find that to manipulate the text, it would be easier to not have these headers, but rather the filled in columns (think sorting by header) so how do you do this the other way? Well, there’s a formula for that!

Turning:

apple Recipe 1
Recipe 2
Recipe 3
Recipe 4
oranges Recipe 1
Recipe 2
Recipe 3
bananas Recipe 1
Recipe 2
Recipe 3
Recipe 4
Recipe 5

Into:

apple Recipe 1
apple Recipe 2
apple Recipe 3
apple Recipe 4
oranges Recipe 1
oranges Recipe 2
oranges Recipe 3
bananas Recipe 1
bananas Recipe 2
bananas Recipe 3
bananas Recipe 4
bananas Recipe 5

You begin by once again adding in a new column for the calculation and then filling it with the following:

=(IF(A2=””,B1,A2))

A B C
1
2 apple =(IF(A2=””,B1,A2) Recipe 1
3 =(IF(A3=””,B2,A3) Recipe 2
4 =(IF(A4=””,B3,A4) Recipe 3
5 =IF(A5=””,”B4,A4) Recipe 4

In order to make this work you need to have a blank row above your first row so  the calc can always reference the row above. So this calculation says if the row to the left is empty, then make this cell the same as the one above it. If it isn’t empty then make this cell the same as the one to the left. Again remember that to do any sorting or manipulation you first need to change column C from a set of calculations to actual text data (by Paste Special) otherwise changing the order will just make it recalculate based on the new data in the column to the left.

Favorite Excel Formulas

Since I originally started this blog to help me keep track of coding and computer-related info, I thought I would write down some of my favourite Excel formulas for posterity. Most of these reside in an excel spreadsheet I have carried with me for over a dozen years but here they are appearing on the internet for the the very first time!

Last Name First Name

Simple and prevalent but a lot of people just don’t understand how handy spreadsheets can be for manipulating textual data and do it the manual way.

Bill to Name First Last
Blow, Joe Joe Blow
Simmons, Mike Mike Simmons

 

Bill to Name First Last
Blow, Joe

=RIGHT(A2,(LEN(A2)-SEARCH(“,”,A2,1))-1)

=LEFT(A2,SEARCH(“,”,A2,1)-1)

Simmons, Mike

=RIGHT(A3,(LEN(A3)-SEARCH(“,”,A3,1))-1)

=LEFT(A3,SEARCH(“,”,A3,1)-1)

A few things to know about calculations

  • they all start with the “=” sign.
  • calculations are often made up of many, nested, formulae.
  • you refer to specific cells by their coordinates in the grid. “A2″ refers to the cell in column A, row 2
  • spreadsheet calculations are hierarchical, just like you learned in grade school. So brackets and nesting brackets are important. The spreadsheet will solve for the deepest level first and then work outwards.
  • Things in quotes are treated as text; things not in quotes are treated as elements of the formula.
  • Data elements within a formula are separated by commas

So, in the above, you first need to visualize the problem. Essentially I want everything after the comma to go in column 2 and everything before the comma to go in column 2. So that means first finding the position of the comma.

=SEARCH(“,”,A2,1)
search (for a comma “,”, in cell A2, and look for the first one to appear)

The syntax for this formula and all the others that Excel uses can be found by hitting the fx button right by the formula bar—there are a bazillion of them. Once you’ve found the comma you can then grab everything to the right (or left) of it. To do that you need to subtract the position of the comma from the overall length of cell A2.

=LEN(A20)-COMMA POSITION

To compensate for the space between the  comma and the first name you add a -1 — be sure to remember the brackets because you are subtracting the 1 from the whole calculation.

=(LEN(A2)-COMMA POSITION)-1

So now you have the elements necessary to use the right formula whose syntax calls is Right(text, number of characters).

=right(A2, (length-comma position)-1) or
=RIGHT(A2,(LEN(A2)-SEARCH(“,”,A2,1))-1)

To find the last name is almost the same but using the Left formula, which has similar syntax to RIGHT. It’s a bit easier because you don’t have to subtract from the length to find the number of characters. You still have to subtract 1 to account for the comma itself.

=left(A2,position of the comma-1) or
=LEFT(A32,SEARCH(“,”,A2,1)-1)

Simple huh? I know, I know, it is complex and mind numbing. That’s why I generally figure these calcs once, store them in a master spreadsheet and then reuse them when necessary. Remember that Excel’s fill down command will allow you to repeat the calculation for as many rows as you need and will automatically update the cell numbers to accommodate you.

Putting it Back

Changing it back is even easier.

First Last Bill To Name
Frank Jones Jones, Frank

=B2 & “, ” & A2

Cell C2 is equal to cell B2 plus a comma and a space (in quotes because they are text not data) and then cell A2. Simple!

Twitter Digest

Video-Audio

I spent 6-plus days putting all my video and bunch of stills into a 20 minute-long video of our trip to the Broughtons this spring. Then it took about 5 hours to render and another 5 to upload to youtube.

And then YouTube stripped all the audio because three out of four songs were owned by Warner or Sony and they won’t grant usage for people to use them. Aaaargh! Word of warning people: use EMI’s artists in your video creation; they just offer to sell the song to you rather than demanding you strip it out. Since I made this specifically for YouTube because I had found a dearth of Broughton-related videos earlier in the year this is a bit of a annoyance. I might, might mind you, go back and replace the audio with YouTube ‘approved’ music now, but really… sigh.

So here is an smaller, illegal version hosted on my own server. Get yer popcorn and experience 20 minutes of stultifyingly—and slow—boring video of water, trees, boats and some dolphins (towards the end). I think if you click on the direct link in most browsers you can watch it in its native 1280 x 720: http://macblaze.ca/wp-content/BroughtonsSM.mp4

 

P.S. I did it mostly to bang some rust off my Premiere skills so don’t be too critical.

Whipped and Spliced

One of the (sailing) skills I don’t yet have is good rope work. I like knots and love to fool around with rope but I don’t know any advanced sailing stuff. So I grabbed my old trusty length of nylon (she’s been with me since the Hole’s days and was once a covergirl) and tried to do an eye splice. It didn’t turn out completely horrible but it’s not pretty.

IMG_4197.JPG

To try and tidy it up I also whipped the end. I used minty dental floss: mmmmm, minty fresh! The finished result is quite sturdy but in need of some reworking to truly be as strong as possible. Remember, every time you bend a rope, it loses some of its strength! I will either cut it off or try the other end next. I also could use a stiffer rope as this soft nylon unravels a bit too much. If you are interested in trying yourself,  Animated Knots is a great site to learn from.

Another knot I ‘learned’ recently was the water bottle knot (jug sling or bottle sling). It’s an ancient knot used to carry bottles or jugs by their necks. Handy!

IMG_4198.JPG

IMG_4199.JPG

How Johnny Depp carries his rum.

 

The thing about learning these knots — any knots —is it takes a lot of repetition to gain a mental understanding of the knot and build the muscle memory to be able to retain it even after extended periods of non-use. I’d say about half of my old climbing knots are still second nature to me and the others I have to think about first. There are even one or two more obscure ones that I just can’t remember. So ya, practice makes perfect…