The selection contains multiple data values… Merging cells in Excel.

This is a super powerfeature I have always missed in Excel: the ability to join the content of cells without losing any data. Sorry, what’s that?? Yes, I know about the ‘merge’ option. It’s lame! Try merging two cells that both have content. Excel answers, delightfully happy, that you will only keep data in the first cell and lose all of the data of the other cells, and go deal with it.

multiple-data

multiple-data

So, I decided to ‘deal with it’ and I present here several macros for your pleasure and entertainment that will solve this once and for all.

Merging, Joining and Combining

Well, that’s all the same of course. Let’s start with a simple macro to join a bunch of cells and put the result in the first cell.

Sub Join()
   Dim out As String
   Dim c As Range

   out = ""
   For Each c In Selection.Cells
      out = out & c.Value
      c.ClearContents
   Next
   Selection.Cells(1, 1).Value = out
End Sub

How does it work? First, we define the variable out which will contain all content. Then we loop over all cells in the current selection and we combine their values using the concatenation operator “&”. Finally we put the value of out in the first cell of the selection.

If you have never created a macro before, you can add them by pressing ALT+F11. After that, make sure to copy this one to the personal macro workbook.

In some occasions I needed to merge, but also keep the data separated by either commas or newlines. So I made a slightly modified version with arguments. Note for noobs: you can’t run these like normal macros, you’d have to go into the VB editor and start them from the immediate window. Or, you can make a short calling-macro similar to the JoinWithBreaks below.

' join the contents of a selection of cells
' and put all of these together in one single cell
Sub Join(Optional s As String = "", Optional wrap As Boolean = True)
    Dim out As String
    Dim c As Range
    Dim n As Integer

    out = ""
    For Each c In Selection.Cells
        n = n + 1
        out = out & c.Value
        If n < Selection.Cells.Count Then
            out = out & s
        End If
        c.ClearContents
    Next
    Selection.Cells(1, 1).Value = out
    Selection.Cells(1, 1).WrapText = wrap
End Sub

Sub JoinWithBreaks()
    Join vbNewLine, True
End Sub

Fillerup

Another problem closely related to the merge problem is when you have a sheet looking like this:

excel fill problem

excel fill problem

And let’s say it extends a long long way to the bottom for your archive of ten years. To create a usable Excel table and e.g. to create a pivot table your table needs to have values in every cell, not just the first one. Humans can reason that cell A3 also belongs to January, but computers and thus Excel cannot. Clicking on the autofill handle in cell A2 fixes this, but doing so for 100 cells is still very tedious work. For that, we can use the following macro, which will automagically fill every empty cell with the value right above it.

Sub Filler()
    Dim c As Range

    For Each c In Selection.Cells
        If IsEmpty(c) And c.Row <> 1 Then
            c.FormulaR1C1 = c.Offset(-1, 0).FormulaR1C1
        End If
    Next
End Sub

How to fix the document map in MS Word 2007

The Document Map! That horrendous, horrible, horrific horror! It never does what I ask it to, it never shows what I tell it to show, it constantly messes itself up beyond recognition. It’s like a crazy guy beating himself up, I keep thinking of Fight Club. The first rule of Document Map is you don’t talk about Document Map. And yet, I am going to talk about it.

Today I suddenly found out the magic trick, the silver bullet that fixes the problem that had haunted me for so long.

As any professional MS Word user, I use the map extensively, even though it’s quirks pester me constantly. It’s just to powerful to let go.

What are it’s problems?

  1. Outline works, as long as you have very strict usage of headings. A lot of documents fail this, especially if they aren’t created by yours truly (ahem).
  2. Titles show no outline numbering, even though it’s definitely there. This is more an annoyance then an outright problem.
  3. The map is not updated. Grrrrr.

It appears Microsoft has put some fuzzy logic in the document map. Now most of the time I don’t really care about MS’s guess at what I want to do. If I press TAB then most of the time I actually want an indent. If I type ‘Teh’ I do mean ‘The’. And of course, I know how to disable that behavior.

However, the document map seems to magically create non-existing headings from my text, and not in the farthest corners of options and settings is there mentioning of the document map doing this. It’s like an easter egg, but not a very funny one.

And then I did what I often do when cleaning up documents. I pressed “CTRL+Q+SPACE”. If you don’t know this trick, it’s the ultimate cleanup action. It removes all and every formatting. I had the map open because I was about to do some things with headings when I noticed after pressing ‘CTRL+Q’ that the item disappeared from the map, as it should because it wasn’t a heading! Then, by not continuing with CTRL+SPACE I retained the original formatting (bold in this case).

Before you select all text and press CTRL+SPACE remember it wil turn lists into regular paragraphs, so make sure to just do this on the items that need to be removed from the map. If that still doesn’t work continue to press CTRL+SPACE. You will lose formatting, which you should recreate using styles. And if that fails make sure to press the Normal style button to revert to the default formatting.

The second problem appeared to actually be a bug. A heading right after a page break will not show its numbering. Well, that’s minor and you can always select ‘page break before’ in the style settings.

The third was related to both 1 and 2, perhaps it won’t happen again. I hope so, I really do.

Try it out and let me know of any other problems you encounter with the document map.

Update: someone created a macro for fixing the document map!

Fantasy Selling

photo of earth from NASA

The earth is FLAT! Seriously.

Here is a little mind control technique I just thought of: selling/planting an idea into someone’s head by just telling random stuff. You’d have to make sure to put enough symbolism in it, so it enters the brain of the other person through the power of their own associations. Make sure to bring in scientists, religion, government and media. Those are powerful friends to convince people.

The earth is flat

Goal: sell the idea that the earth is in fact flat.
Script: “Hey, you know the whole thing about the earth being a globe is in fact a hoax? Yeah, I didn’t believe it at first, but apparently it’s true. So when you see that image from space it’s always a circle but there is no shadow, so you know it can’t be round. Also, in the bible nobody says the world is round. In fact, in Daniel 4:10, “he saw a tree of great height at the centre of the earth…reaching with its top to the sky and visible to the earth’s farthest bound”. If everyone could see the tree, the earth must have been flat. Also, research has shown that people believe anything on television, and the images from space usually don’t show a moving earth: it is still, fixed as the bible says too. The reason you see that same picture of the earth from space again and again is because the media and the government need to sell this idea so bad. That’s why they need to repeat it, otherwise we would quickly fall back to the truth, which is that the earth is flat. “

A physics model of a physics model

One of the hardest topics in school next to computer science is physics. Time and again I see students struggle with the topics, and I have to admit I didn’t find it easy in high school myself. So I started studying physics again using Khan Academy, and gained much insight into the basics. I have admired Sal Khan since he started his site, and as a school teacher I also think I should help him, and my students, wherever possible.

I started drawing a diagram of how all things in physics connected to each other, and while doing that, I realized there should be software to do this better. After a short search I found the wonderful D3 toolkit, based on web standards, just as I like it. D3 can create awesome diagrams, graphs, infographics, you name it.

So now I have for your pleasure and learning aid a physics based overview of common physics quantities. Click the image to see the actual animated model.

Some tips on how to use it:

  1. Click the diagram to automatically redraw it
  2. Drag nodes to change the layout
  3. Mouseover a node to see related info
force diagram physics

force diagram physics

Use in physics assignments

The colors in the diagram denote related quantities:

  • blue – essential axiomatic units m, l, t
  • green – related to movement
  • orange – related to energy
  • red – related to a single unit

To use this diagram in your physics assignment, look at the known variables. Find those in the diagram. Now see if you can combine those to make more variables: follow the arrows. If you can start from two known values you can usually calculate a third. You continue doing this until you have found the desired outcome.

Salman Khan is the smartest man in the world

The world is changing. At some point in the future, says Ray Kurzweil, computers will be more intelligent than humans. He thinks it will happen in the year 2045 to be exact. That’s a scary thought, but he could be right. Many say he is the smartest man in the world.

But today I realized that Salman Khan is ahead of him. Khanacademy is an online tutorial for everything including math, biology, economics, chemistry, physics, history, and more. And Khan made all videos himself.

But not only did he do that, he is expanding at an exponential rate, to offer his courses to classrooms. And that could revolutionize education.

And all of that… for free, because he is filthy rich anyway, and doesn’t care about making more.

World changing: Khanacademy. See for yourself.

Facebook limits you at 5000 friends

enough is enough

enough is enough

Jeffrey Zeldman claims 5000 friends is not enough!

Not Amen! Agree to disagree!

The value in a social network lies in social. So the friends should be people you have met in real life and value in your social circle, which revolves around your life, not your business. A page is a business tool so that is what you should have.

The cap may be nonsense, but unfortunately there is no way for Facebook to validate that you actually know the ‘friend’ personally.

One other side-effect in which Facebook bites it’s own hand is that apps and games (OK and businesses) thrive by the amount of ‘friends’ you have. That is an incentive to make fake friends. I started to play CityVille only to find out that none of my friends did. If I were true to Facebook’s dogma I would quit playing or be an obnoxious friend who would evangelize CityVille to all my friends all the time, until they unfriend me. Now I play it happily with over 30 fake friends whom I’ve never met. I even block them from my wall, as I don’t want them in my real social circle. Once I stop playing CityVille I will quickly unfriend all these people.

Still, I fully agree that Facebook should work on a solution, which in my opinion is definitely not removing the cap but some way to have people in your network for a certain purpose, and have great distinction in how you treat and communicate with them.

All the positive talk about Twitter’s agility: don’t become so vigilent, or should they also remove the 140 character cap!?!?!?

I have always been appalled at the nomenclature of new applications: Friend and Like come to mind. I call them ‘close-related-marketing-profile’ and ‘increase-marketing-keyword-potential’.

Jeffrey, you don’t get to  5000 friends without making a few enemies :-P

Will Readability 2.0 change the web?

Jeff Zeldman writes a compelling argument for Readability 2.0. Readability is an open source tool that transforms a web page to a reading page, removing all clutter, including ads. Readability 2.0 will now actually pay the content providers (website owners) every time a user switches on Readability to read their pages.

At first I thought: Does this mean publishers don’t need to sign up?? So they get an unexpected check from a seemingly philanthropic web company?? But after looking at the site, I realize publishers do need to sign up. And that, I think, makes it quite unpractical. Let me tell you why.

How about promoting Readability on a site with ads? That’s like saying: click here to screw with all our other clients. So that’s virtually impossible.

I believe we can divide the site owners in four groups

1. The small site owner, not being able to get advertisers easily, not making enough on Google Ads. They could promote Readability, but wait: there are no annoying ads on the page. Duh. What’s the point? Well, to be honest, they could litter the site with Google Ads and then have a shiny button that says : “don’t like ads? click here to remove them”. The user would then learn about readability, sign up and start paying.

2. The large site owner who is in a clinch with advertisers, and says: we’ll just move our whole platform to readability, @#$% you very much. You pay Readability, after which you get a ‘pro’ account which gives you access to more content. That would require a partnership with Readability I guess, unless there is a technical solution (checking to see if you are using Readability).

3. The large site owner who has littered the site with ads, who likes Readability too, but is unable to promote it, fearing the wrath of advertisers. There’s no way for this owner to put a button on the site.

4. Like Zeldman.com, the large site owner who is not in it for the money, or actually, makes enough doing other stuff and just uses the site for self-promotion. They don’t have ads, and using Readability is nice, but unnecessary.

I am dying to see some stats soon, so we can see if offering Readability instead of advertisement is a valid business decision. If it is, it will spread and transform the web. At some point Readability then needs to be taken over by a big player, or it will be simply sued to death by angry advertisers.

Why is writing for the web difficult?

Blogging for dummies

I have been a web professional for over 10 years. In that time I have learned a lot of things: PHP, CSS, XHTML, JavaScript, XML, Photoshop. But one of the things that has always been harder to do right is writing for the web. The reason? It has so many conflicting motives, that it’s impossible to have one good writing style. Let’s analyze:

When we write for the web we most certainly write for the fast paced audience that has no time to read pages and pages of text. That’s a given, and it’s what sets it apart from writing for paper. The other characteristic is links: if you need to explain something, it’s easier to just link to wikipedia than to insert the actual text. Also you don’t want to infringe on copyright.

Let’s look at our goals for writing for the web:

  1. Make it easy to read
  2. Make it easy to use
  3. Make it easy to find (search engine position or ranking)
  4. Make it sell (in case you’re selling, but you’re always selling ideas)
  5. Make it look good

Those goals are also connected to viewpoints from certain people: the writer, the usability expert, the SEO specialist, the sales representative and the designer. These fantastic four have to make sure the page adheres to all their standards. And here is where it starts to itch:

  1. easy to read: plain fonts, short sentences, short text, clear message, bold, pictures and diagrams that explain difficult topics.
  2. easy to use: minimal use of text, clear and large buttons, minimal use of design elements
  3. easy to find: clear headings, short sentences, keywords in bold (not the same ones), no pictures required.
  4. sell:  everything leads to a buy, no navigation on cart page
  5. look good:  creative fonts, no bold text, plenty of  non-illustrative pictures (preferably photographs) and design elements

In my opinion you should work from the outside in: make it look good, then make it easy to use, easy to read, easy to find and easy to sell, in that order. But as you make it look good (design process) you can of course have some consideration for the other aspects. It should also be noted that SEO is still voodoo, since nobody knows how Google really works. Also, search engines, in particular Google, change their ways of working constantly and they get better and better at identifying your pages. So don’t try to fool them, it’s not worth it.

There will probably always be a battle between usability experts and designers, because their worlds are so far apart. But to a modern web user something like useit.com looks like it was made 20 years ago and doesn’t instill trust in a user, something that is vital to sales.

Remember I may not be an expert in all those areas,  but the ideas presented do come from the leading experts in these areas.

Why I don’t like the Facebook Like button

thumbs up

thumbs up

First of all, I hate the “Like” button. However, I have to be honest, I hated the “fan” button even more. Now I am not just being negative. Let me give you some background on this.

The Fan button

The first time I saw the “become a fan of ….” button on Facebook I didn’t know what to think of it. I started thinking, OK, so what am I a fan of. I soon realized it’s a very short list, that also changes over time: Rubik’s cube, Aikido, House, Monk, Rush, Led Zeppelin, and some others some to mind. But to say I am a fan of e.g. Harry Potter is quite exaggerated. So, reluctantly I clicked the fan button for “House” (the TV show, not just any house). The next thing I knew I found myself drowned in messages about (from?) my temporary favorite TV show. It’s just plain stupid. I do want people to know I like the show, but not get stupid messages all the time from it. So I stopped being a fan of anything else.

The Like button

I was happily surprised when Facebook decided to change the Fan idea to a “Like” idea, thinking they must have heard some voices echoing my thoughts. Unfortunately though, the button still does the exact same thing: squirting out a barrage of nonsense messages. I soon found out though that now it’s quite easy to hide those messages without losing your ‘like’ status.

Google Reader

Another thing I am fan of is Google Reader. Ever since I got a new PC I never felt like installing applications. I realized that even though compiled software is faster, and sometimes has really nice features, using online software just works anywhere, and is good enough for me. Also I have been moving around between computers more, so it becomes quite necessary to synchronize application status. But that’s another story.

Google Reader has had a “like” button perhaps even longer than Facebook (can anyone tell me if that’s true?). And since Google Reader doesn’t followup the click of a “like” with a barrage of nonsense messsages, I was happy to click “like”. After a while though, I found out that on the top of my ‘suggested reading list’ were a hole bunch of really stupid posts. I thought: “there must be something wrong with Google’s algorithm”. And then it hit me: Google uses the “like” button to learn what you want to read. But I had been using it all wrong. I clicked “like” usually when something was really, really funny. And so Google decided I was mostly interested in humor. And that’s pure BS. I like a joke once in a while, but not all the time, when there’s interesting serious news ahead. And it was impossible for Google to tell what I found really hilarious and what I found just plain stupid (a sense of humor is not a computer’s strong point).

So I revised my like-button-decision-making-process and was happily surprised when only weeks later Google had adapted itself back to a more serious note, and I could suffice with reading the top 50 or 100 items, and send the rest of the news to a peaceful death.

Still, I was faced with another issue: once in a while I read interesting news about natural disasters. But I found it morally appalling to mention to Google that I liked the fact that over 200,000 people died in Chili, or that dozens died in a freak accident. But I learned the hard way now it’s wrong to think that way, we have to redefine the “like” button to an “adjust-neural-net-weight”-button, even though that sounds a lot more complicated.

Now what’s next?

I am going to continue clicking ‘like’ for everything that I wish to associate myself with, or wish to receive messages about in the near future. My advice to readers: please realize that there is a commercial incentive to all this ‘liking': ads will always be tailored to the user, wherever possible. And Facebook nor Google can do this properly when it doesn’t know what you ‘like’.

So far, I like where it’s going, though the wording is wrong in every single way.

Cybernetic Programming

cybernetics

I’d like to make a proposal for a new kind of programming (at least new to me, LOL). When I was studying back in the nineties I became interested in something called ‘sociocratic organization‘. This is a form of organization based on Cybernetics. Cybernetics is the study of systems with feedback loops. When you think of a thermostat in the room, it has a feedback loop. When it gets too hot, it cools, when it gets too cool, it warms up, and that way it keeps the system in balance. In computer programming we don’t deploy such features normally. We pray that all goes well, and that we didn’t forget anything, and if it does go wrong, the whole system usually crumbles apart. In web programming that’s usually a minor glitch, since just one page of our whole system breaks. In desktop computing it’s much worse. And in embedded programming it’s disastrous. Now instead of writing numerous unit tests, or mathematically proving our program, perhaps it would be possible to use this idea of a feedback loop in many aspects of our programming.

Consider you have to write a program that is to determine the best results for a search, similar to Google (but not as vast, so let’s say a small web site). We need to display what’s relevant to the user, but we don’t know what is relevant. So you just do a simple text search to find a record within a table. Now you check which of the results is clicked for this particular search result, and then you log the search term, and with it the clicked result.

Then a new user shows up and does the same search, and clicks the same result. You increment the counter for this result on this search in the log. After a while we should see our statistics in the table represent what people were searching for, and which records were ‘successful’ to them (at least they clicked it). Depending on these results you can now decide to alter the order of the search results, where the highest scoring record is put on top of the search results page.

Pretty nifty. I Googled it and found that Microsoft Research has written a paper on something similar. So expect this to Bing us soon.

Follow

Get every new post delivered to your Inbox.