How to turn two columns in Excel into one without losing contents - concatenate not merge

To be honest I'd assumed the Merge function would sort that out, but no, it creates a single cell from two cells but deletes the content of one and so leaves you with half of your information. Merge is not the function you're looking for.

It turns out that it's Concatenate.

Here's a picture of what I had and what I wanted.

Obviously my first port of call was Twitter.

@JoBrodie make a third column and use concatenate, so basically C = concat(A,B) http://t.co/6MO2aDoZKj
— Lauren G (@geeoharee) October 14, 2014

@JoBrodie = A1 & " " & A2
— Spike (@tenbus_uk) October 14, 2014

I used the second suggestion and it worked, hooray.

Short Task - where people ask you to do random things for tiny amounts of money

Short Task is an odd website, similar to Amazon's Mechanical Turk and it is full of people making strange requests of other people for small cash payments.

This one's pretty specific (click to enlarge). It wants you to go to Google and search for "non slip rug pad ruglock spray" and say the name of the video and the product's price on Amazon. For this you get 7 cents. Obviously doing this involves running a search which presumably increases Google's indexing of this product but isn't that a strange advertising strategy?!

Not having any psychic ability... here's how I'd do it.

Genuine psychics can apparently rely on dead people communicating directly (though rarely clearly) with them. I've had experience of listening to an Edison wax cylinder recording made from 1888. It was very muffled so I expect hearing from people who've passed over is somewhat similar. I'm also not certain how you 'hear' from someone who's died as their vocal cords won't be working, but such is the mystery of communicating with those who are no longer here.

I don't have the gift of speaking with the spirit world so I'd be unable to do that without some sleight of hand. Here's how I'd give people the impression that I can do it though - it's almost the same thing I think. Obviously this is a thought experiment and I won't actually tell people that I can talk to their departed loved ones as it seems cruel to do so if you can't actually do that.

1. Local and national newspapers' archives (online, libraries)
First, I'd find out who's died locally within the last year. Most towns have a local newspaper and their archive is online. I'd search the name of the city / town and add 'local paper' or 'local news' and see what comes up. Noting the web address(es) of local paper(s) I might expand the search with something like:

inurl:localnewspaper.co.uk died
or
inurl:localnewspaper.co.uk death - and you can use Google's search features to restrict to within a particular time period.

I might also just try the name of the town and 'died' and variants of that. And of course mainstream newspapers' obituaries (almost all online).

2. If you build it they will share their data with you
Probably I'd have already set up a Facebook page or Twitter feed so that people could tell me which shows they were coming to - a few scrolls through their profiles ('Facebook creeping') might tell me if they'd lost someone (or even if their friends had lost someone) and who that person was.

Someone on Twitter might have mentioned a few months ago something about someone they'd lost and that wouldn't show up on looking at their current profile, fortunately Twitter's advanced search lets you go back much further in someone's tweets.

I'd probably also have a sign-up form on my website with a field for people's postcode. That tells me their city and street, and in some cases narrows it down to a few houses so I can have a good idea of where they live and perhaps what amenities are nearby (perhaps a local church, might take a wander and look at the headstones if I'm in the area). Their email address might not be very instructive (gmail.com) but maybe they'll use a work email address so I'll know where they work. If I sold things on my website I'd have their bank information too, might be able to get further info from that.



3. Advanced trolling
There used to be a magazine psychic who asked readers to send in a photo, name, phone number and SAE (stamped addressed envelope). I wonder how many of the people writing in understood what information could be gleaned from that, though this was a few years ago in the earlier days of the internet when finding info out would have taken a different set of research skills.

Another person, Peter Popoff (an evangelical preacher / 'healer'), had a clever technique of (a) getting people to fill out prayer cards at his events with their name, address and afflictions and (b) getting his wife to communicate this to him by an in-ear microphone. Of course the audience at his shows didn't know about this in-ear microphone and believed that he was receiving this information through divine means.

4. Further reading
Techniques used by psychics include psychological tricks such as cold reading (making reasonable guesstimates about you from your appearance, and the things you say and contribute to the conversation) and hot reading (doing a background check on you, foreknowledge).

I might also familiarise myself with some examples of 'jigsaw identification' which shows how bits of information that are not obviously related can be used to triangulate other bits of information.

I am totally recommending the musical Made in Dagenham

by @JoBrodie, brodiesnotes.blogspot.com

Edit 12 Oct - at the end there are (1) instructions for how to get the £15 tickets (there are 150 Upper Circle tickets available at £15 for every show during the preview season) and (2) an embedded Storify that @MadeInDagenham made from some of the lovely tweets received after the first couple of previews.

I had a fantastic time at Made in Dagenham on its Thursday opening preview night :D It's ace. It's also really, really funny and astute, and full of lovely little touches. The theatre, Adelphi on the Strand, was packed and very warm (glad of an interval ice cream) and I plonked the scarf I'd brought in my bag and rolled my sleeves up.

A picture I took from outside the theatre

While I'm keen to avoid spoilers the set design is one of the most startling things I've ever seen - it all seems to be cunningly rigged with pieces coming in from every direction (in from the sides, up from the floor, from above, from the back and things moving backwards). That's when it hit me - that's where dark rides have gone! (Those things where you sit in a moving capsule / seat and get whizzed around historical tableaux, loved them).

Previews are a little like the theatre equivalent of a soft-launch or a sort of postmarketing surveillance - a way to find out what works when an audience is in the room, so the show will likely change after a bit of tinkering during the previews run, until early November. Don Black sent a text to the director, Rupert Goold, wishing everyone well and saying that a preview is a bit like having sex with the light on. You'll still enjoy it but you might see things that perhaps you're not meant to ;) Other than a small technical hitch, quickly fixed within minutes, I think everything went perfectly.

I'm curious to see how things will change once previews are finished but I'd want to go an see it again anyway. For someone who's not been to a musical in 30 years and who rarely goes to the theatre it's gloriously overwhelming with all the singing, dancing, acting, live music, visuals - it's a very visceral experience and I probably missed bits from just being so surprised and delighted by it all.

There are some beautiful tunes in there, no surprise as the fantastic David Arnold is behind the music (he's doing a couple of concerts next year and an interview at RAH in December) - the two chaps sitting next to me were singing along as best as they could (don't think they knew the words) and I heard a bit of singing in the ladies loos too. Everyone was humming the chorus to 'Everybody out!' in the interval.

The lyrics for all the songs are clever and funny (and occasionally ribald) - I think my mum would have enjoyed the word play. I had no idea so many things rhymed with Made in Dagenham! Gemma Arterton does indeed have a fantastic voice and is brilliant in the show.

One of my favourite songs from the first act was... well if I write it I'll give too much away... it's a scene in which one of the women who seems a bit dithery tells us what she'd say if she went to a meeting with management to discuss the news that the women workers at the Ford plant in Dagenham had been downgraded from C to B (unskilled workers). Turns out she has plenty to say and I thought that song was cleverly done.

A picture I took from inside the threatre

Special mention goes to everyone really - I didn't really know who Steve Furst was other than I already like him because he co-founded the amazing Double Six Club that kept me amused in my early 20s but he's fantastic as the brash American from Ford HQ in the US. We all enjoyed booing him affectionately at the end. There's a wonderful double act with the people playing Harold Wilson and Barbara Castle - some exceptionally funny scenes there. I'd like to have heard more from the 'maid' character who seemed to move silently through a number of scenes, playing different characters I think (but always clearing up after people) - she was great.

An example of the amusing little touches happened just before the curtain rose on the second act - we were taken back in time with some 1960s-style adverts for shops on Dagenham High Street, loved that. There's a beautiful song in the second half that I was surprised to realise I recognised and was trying to work out how (other than 'Everybody Out' I'd avoided listening to the songs they'd made available as I wanted to experience it fresh), then remembered that David had sung it at his concert back in July. Despite being in a very warm room I had to roll my sleeves down - goosebumps.

The narrative doesn't follow the film exactly but the story's the same - the women machinists who sewed the seat covers for the Ford cars found their pay had been unfairly downgraded. The management had determined that their roles were unskilled but as each machinist had to pass a number of tests to get in the factory (and once there they had to sew pieces of fabric together without a template or instructions) it was clear that skill was needed. While the story starts out being about being properly acknowledged as a skilled worker it soon becomes about the wider theme of overarching pay inequality between men and women. The real-life women involved in the story were fighting for fairness and equality in pay and their actions eventually led to the Equal Pay Act 1970 (superseded by the Equality Act 2010).

After the show I realised I was standing right next to David Arnold so I introduced myself and congratulated him on a fantastic musical but I was a bit too shy to say hello to Stephen Woolley who was nearby (he produced the film version, which David also scored).

It's a great show and I hope it has a long and successful run, and I hope they're all really proud of themselves and are breathing sighs of relief :)

Further reading
A Women's Worth: the story of the Ford sewing machinists notes by Sue Hastings (2006), click on the PDF icon on that page to open the four page document.

In 2013 The Fawcett Society and the TUC (and other unions) marked 'Equal Pay Day' on 7 November (this year it falls on a Friday) - that's the day on which "women effectively stop being paid for the year because of the gender pay gap".

40+ years on from the strike action by the Ford women machinists and it's still a problem. In December 2013 the Office for National Statistics (ONS) released their 'Annual Survey of Hours and Earnings' for the preceding year. Somewhat depressingly the "gender pay gap (i.e. the difference between men's and women's earnings as a percentage of men's earnings) based on median gross hourly earnings (excluding overtime) for full-time employees increased to 10.0% from 9.5% in 2012."

There are 150 tickets for £15 during the preview season
Here's how to get the £15 tickets in case you're not sure where to find them (click to embiggen the pictures and then you probably have to use the back button on your browser to return to this page).

Go to the ticket booking website and select the date on which you want to see the show...

...then choose Upper Circle (thats' where the £15 tickets are)...

...find a circle that isn't white (unavailable) and hover over it to see the price - the pink ones in the image below are both £15 - at time of writing it's just these two left (K3 and N38).

Here's the Storify from @MadeInDagenham

If you give talks with slides you must do this one thing - because I have commanded it :) Please make your links friendly

by @JoBrodie, brodiesnotes.blogspot.com


1. Shorten links in presentation slides because...

Most normal-length web addresses are quite fiddly to write down at speed. If your talk includes a link to a website or an online document please make it easy for your audience to make a note of it within the minute and a half you have your slide visible on-screen.

Eg... the one with the green tick is easier to scribble down than the one with the red cross next to it.

Please shorten links if you want people to note them down

If your presentation has lots of links then first create a page on your website or blog (if you don't have one there are other free suggestions below). Add all your links there, then shorten the address of that page and put that shortened link in your presentation. Your audience cannot help but be kindly disposed to you :)

You can also have that shortened link running over several slides making it easier for your audience (in fact why not say "this link will appear on several pages so if you haven't written it down don't worry").

Links in handouts / documents
If you're giving people a handout with lots of links then to save them typing each one individually a 'master' link can help here. As above, add all the links to a page and then share the link to that page. There's no need to replace the other links, people will like to see all the options and then know that they don't have to type them all in. A single link is a useful addition here, rather than a replacement as in slides.

You can also use link shortening for any individual link in your document, particularly those that are long or stretch over one line. In documents that are emailed the link may well 'work' but part of it might be cut off so the person is being sent to the wrong place. Not everyone knows to copy and paste and 'reattach' the broken address so it's helpful to pre-empt this by shortening the link first.

Getting more information from the link you've created
If you use bitly.com to shorten your links then you can see how often it's been clicked on (or typed in) - this means that you can get an overview of traffic to that page - but only the traffic that has come via your shortened link. You'll get no information about other routes in to the page.

2. Shorten links like a pro
Customise* your link.
Always customise. Ironically short gibberish links are probably worse than long ones that are comprehensible. In the example above I shortened it to 'URLette' (small URL) but the default one would have been a meaningless string of numbers and letters. Certainly quicker to write down, but not necessarily easier - use actual words if possible.

And for advanced users...

Go meta - one link to rule them all
Share one link which takes people to a page where they can find all of your mentioned links
Take your list of links, add them to a page. Take the address of the page and shorten / share that.

When sharing this single link on your slides make sure you shorten it and customise it so that it's easy for your audience to clock it quickly and write it down during the talk. It's also helpful to highlight verbally what you've done and why because there will be others in the audience who might be at future risk of giving lectures with unnecessarily long links. Teach them :)

By convention links are blue and underlined but since your audience can't click on them you are not restricted and can display them as you please. You probably don't need to include the http:// bit but it can be quite useful in directing the eye to something that looks like a link, and for the same reason blue isn't a bad idea either.

I and your future audiences thank you for this consideration.

Further reading
How I prepare presentations for giving talks (18 October 2014)

3. How to shorten your links


Go to any link shortener page, put your long link in and press 'shorten' (or equivalent) to get a smaller one. Take advantage of any customisable options as appropriate. On some (eg Bitly) you can sign in with Twitter.

http://is.gd/ (customisable (look for "Further options/custom URL") but no QR** codes)
http://v.gd/ (customisable, QR codes)
https://bitly.com/ (customisation, new QR codes no longer possible, log in optional)
https://goo.gl/ - Google (QR codes - add .qr to your generated link to get stats, but no customisation)
http://tinyurl.com/ (customisable, no QR codes)

@schrodingerskit reminded me that bitly links actually let you see the statistics relating to how often your link was viewed.


**QR codes - let people 'scan' your link from printed material 
If you're going to put a link on printed material and want to save people typing things then use a QR code ('Quick Response') to let them use their phones' camera to go to the site without having to type anything in. If your URL shortener doesn't come with 'native' QR codes you can create them with other free services, such as http://www.qrstuff.com/

^For people who don't have a blog, some suggestions
 There are numerous sites which let you publish a one-off page. That'll give you a link that you can then shorten / customise and share as before.

Examples include
https://woto.com/ (formerly Cloudpage)
http://sqworl.com/
Storify's not bad either https://storify.com/

If you know of other examples tell me and I'll add 'em.