Formatting cells in Excel if they match a value in another column

This content is 2 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A few months ago, I wrote about using Excel to sort a table of names that had been created in Word. And last year, I mentioned a dynamic report I’d created with a colleague, stringing together a few formulae, some data validation and some conditionally formatted cells. Well, today, that same colleague came to me and asked to borrow my Excel skills again.

He had a list of potential sales opportunities and wanted to highlight any cells in the column of client names that matched a list of primary accounts on another sheet. It sounded “do-able”, with some conditional formatting and a formula.

I like a challenge – and it’s as close to any development work as I get these days – so I got stuck in.

It seems the function I needed was =MATCH() although now I’m wondering if =VLOOKUP() might have been more appropriate.

The actual formula used in my conditional formatting rule (applied to data in column E) was this:

=MATCH($E2,'Primary Account List'!$A$4:$A$34,0)

Basically, it’s saying, for the value in this cell, have a look at the data in the Primary Account List sheet, cells A4-A34 and if there’s a match, apply the formatting (bold, orange). I did put $E1 in at first, and the lookup was one cell out… (row 1 is the column headers). E2 is the start of my list but the same formula is used in the conditional formatting rule that covers all the cells.

Featured image: author’s own.

Another approach to technology roadmaps

This content is 2 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Over the years, I’ve looked at a variety of approaches to mapping technology development, often aligned to strategy.

I’m a visual person – definitely working with maps, not lists. This means I am attracted to models that help to visualise the direction in which things are headed.

Many people will be familiar with models like Gartner’s Magic Quadrants, or Forrester Waves. I was intrigued today when I joined a webcast about the Thoughtworks Technology Radar. Unlike the models I mentioned from the big analyst firms, the Thoughtworks model is not “pay to play”. It’s purely based on the experiences of the people working at Thoughtworks (“the Thoughtworkers”). There are no partnerships to influence – just product/service experience.

How is does this model work?

Thoughtworkers propose “blips”, and these are assessed by a Technical Advisory Board before being placed (or not) on the Radar.

The Radar itself is split into quadrants. These relate to techniques, platforms, tools, and languages and frameworks. There are also four concentric rings for hold, assess, trial and adopt.

Each blip is assigned to a quadrant. It will then move through the rings as Thoughtworkers gain experience and form a view on the application of the technology or service.

Only around a third of proposed blips make it to the published volume (currently volume 27).

The diagram that accompanies this post shows the Radar, but with detailed information removed. To discover what the numbers represent, check out the current volume on the Thoughtworks website.

I asked about technologies that are reaching the end of their life and need to be sunsetted or retired. That’s not something Thoughtworks currently has the resources to manage. Blips are not maintained – they are a point in time view (like a blog post!). If a technology becomes harmful or problematic it may move back to the assess ring or be called out in hold.

Bring your own radar

So, why am I so excited? Well, I think this is something worthy of investigation as a wider tool. Almost ten years ago, I wrote about my experiences of technology standardisation at Fujitsu. More recently, I’ve adopted a model for roadmapping technologies that I have used with clients. I also had a failed attempt at technology standardisation (it lacked the resources or corporate buy-in to maintain and, to be fair, was probably superseded by vendor-supplied frameworks).

But what if I could come up with something like the Technology Radar for the technologies that we my employer uses in solutions? Maybe with Principal Consultants and Architects as gatekeepers? I initially thought that the Radar is Thoughtworks’ intellectual property, used as a marketing tool. Then I discovered it’s also available on an open-source basis to “Bring Your Own Radar” to allow Thoughtworks clients to visualise their technologies.

I probably need to think this through a little more and clarify the value I’m looking to gain. Right now, it’s just a collection of thoughts bouncing around my head but I’m sure it will form some order soon!

Copying a style from one Word document to another

This content is 2 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

My work involves a lot of writing. And document editing. Mostly in Microsoft Word. I’m a heavy user of our corporate template and, over the years, I’ve worked with colleagues to iron out issues in the built in styles.

If you’re not familiar with styles, they are a method to apply standardised formatting to a block of text. Instead of selecting the font, size, colour, indentation, bold, italic, etc. just select a style. The style groups together all of the associated formatting so you can pick Normal text, Heading 1, Heading 2, Bullets 1, Bullets 2, Subtitle, Quote, Emphasis, etc.

if you’ve ever created a website using HTML and CSS, you’ll be familiar with separating the content from the presentation. This is a similar concept.

There’s more information about customising and creating styles in this Microsoft support article.

I sometimes find that the styles in a Word document get corrupted when I’m working collaboratively. Common things I’ve learned to spot are that indentation is wrong on bulleted text, or that headings lose their numbering. I haven’t found the cause but I suspect it’s text copied from incorrectly-formatted documents with similar style names. I did find a fix though!

Faced with a stubborn Heading style that had lost its numbering a few weeks ago, I stumbled on a Microsoft Answers post that told me how to copy a style from one document to another.

The process involves finding an obscure Organizer dialogue:

  1. Open the document you want to copy the correct style from.
  2. In the Styles ribbon group, click the breakout arrow in the bottom-right corner to open the full set of Styles options.
  1. Click on the “A with a tick”, which opens the Manage Styles dialogue.
  1. From here click Import/Export… and it will open the Organizer.
  1. Close the file on the right hand side (we don’t want to use the default Normal.dotm).
  1. Open the file that you want to copy the style to – you may need to change the filter from Word Templates to All Word Documents.
  1. Select the style to copy, click Copy -> and then Close.

Your “problem” document should now be able to use the style you copied across from the “known good” document.

The symbiotic relationship between engineering and architecture

This content is 3 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I’m entering a new phase of life as my children are growing up. My eldest son has passed his driving test, and now we’re touring university open days. He’s looking to become an engineer (as was I, before I failed my A levels and fell into computing, but that’s another story).

Last weekend, we visited the University of Bath, to learn about their Structural and Architectural Engineering course. In his introductory presentation, Senior Lecturer Dr Chris Blenkinsopp was talking about the relationship between engineers and architects, and it really struck a chord with me.

Dr Blenkinsopp was speaking about engineers as “born problem solvers”. Engineers focus on design – following guidelines and using their problem-solving skills. The architect does the big picture “drawing”, the engineer makes it work. Whilst a computer might be able to solve the maths, the engineer needs an ability to use a range of skills in an imaginative way.

Successful projects need collaboration between engineers and a variety of stakeholders. Critically, it’s vital that architects and engineers work together closely. And, for that reason, the University of Bath’s Design Projects involve both engineers and architects – collaborating at university as they will in their professional careers.

Whilst Dr Blenkinsopp was talking about civil/structural engineers and architects who work in the built environment, there are strong parallels with my world of information technology (IT).

Architects and IT architects

I have to be careful here, because I’ve been called out previously for calling myself an architect, which is a protected title:

“The title ‘architect’ is protected by law in the UK, under Section 20 of the Architects Act 1997. It can only be used in business or practice by someone who has had the education, training and experience needed to join the Architects Register and become an architect.”

[Architects Registration Board]

But all of that relates to architects who work in the built environment. In IT, architect is a broadly used term – and is recognised in the Skills Framework for the Information Age (SFIA). It’s also part of the job title in my employment contract!

The relationship between IT engineers and IT architects

Unfortunately, in IT, the term “architect” is also abused. It’s become common as a term to imply some seniority in the technical space. As a result, it’s lost some of its meaning. Even so, my role as an architect is less and less about technology and more and more about solving business challenges. In the course of that work, I work with lots of subject matter experts – the engineers of the IT world – who solve the problems that I give to them. My role is to draw the big pictures and join everything together. [Often, my tools are some whiteboard pens…]

Where I work, at risual, we run Consulting Skills Workshops, to help our subject matter experts develop the soft skills that are required to be a successful consultant. In reality, our consultants are on the first step towards IT architecture (whether they know it or not). Consulting is an engagement model and a set of soft skills. In terms of career progression, our consultants are no longer engineers – they are often required to work as technical architects.

But there is absolutely nothing wrong with being an IT engineer. We need those problem solvers – the people who know how to bring technology together and use it in imaginative ways. Just as much as we need the people who can take those technology building blocks and use them to solve business challenges.

Conclusion

As a result of taking my son to Bath and sitting in Dr Blenkinsopp’s presentation, my mission has changed. The work I’m doing at risual to develop and grow our Architecture practice needs to be tweaked. I need a slightly different focus. I still need to create great architects. But I also need to up the emphasis on constant collaboration with great engineers.

Because, to take a quote from Dr Blenkinsopp’s talk:

“The best […] engineering solutions require engineers and architects to work together from the outset.”

Professor Ted Happold

Additional Reading

What is IT Architecture? (part 1 of a series I wrote last year)

Developing IT Architecture Skills (part 2)

So, you want to be an Infrastructure Architect? (very old, but contains some useful diagrams)

Featured image: author’s own.

The paperless office

This content is 3 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

For decades, the paperless office has been a panacea, sought out by businesses far and wide. Ever since computers became a part of our daily work, there have been those who have prophesised on how we will no longer need to use paper.

And yet, I still receive so much of the stuff. Sure, I can receive most of my invoices and statements electronically – and we all know how print newspapers and magazines are in a state of decline – but paper is still used extensively. Some people just prefer to interact with analogue media – my wife likes a paper book, for example, and never got on with a Kindle. And some business models rely on shoving pieces of paper through the letter box (try opting out of Royal Mail’s “junk mail” deliveries and you’ll find it’s a lot harder than it should be).

Digitising my life

Several years ago, I started to scan important documents at home. The theory was that everything gets scanned, and only the really important papers got filed in paper form – generally those that related to the house (mortgage, building works) or to me as a person (passport, certificates, medical, etc.).

The trouble was, that I got behind on my scanning. Years behind. Boxes of “to be scanned” and boxes of “might have been scanned – who knows”. And my old Canon scanner was not up to the task – too slow and with an unreliable document feeder. It was also connected to an old (slow) PC, and needed two powered USB ports to drive it (which was a problem on any of the newer devices that I had access to). Added to which, there’s still all the pre-scanning regime files that were stored in the loft until we converted that to living space (4 years ago…).

So, whilst I took a few days off over half term, I bought a new scanner. A 35 pages a minute super-duper Wi-Fi connected paper-eating monster from Brother… and I reworked my document scanning workflow. I’m now scanning through the backlog and thousands of pages of paper are being shredded and recycled each week. So much that I keep overheating the shredder!

What’s the point?

All of this is good – it’s making me feel good about the progress I’m making and my family will be pleased to have fewer boxes of paper in the spare room.

But then my friend Matt Ballantine (@Ballantine70) remarked that he didn’t understand – what did I need to scan? The only paper he gets in junk! And I started to wonder if I’m somehow unusual?

Matt has a point. Years of “business transformation” and “digital transformation” has meant that most of the companies I deal with offer options for electronic bill and statements. But not all:

  • Not all of my tax paperwork is digital. Increasingly it is, but not all.
  • I keep a copy of the latest Council Tax bill, and at least one set of recent bank statements for identity purposes. (Some organisations still won’t accept digital versions!)
  • DBS certificates, vehicle “log book” (V5), etc. are all paper documents (and odd sizes too).
  • Add to that the letters relating to investments, pensions, banking, etc. that don’t arrive digitally.

It’s still quite a lot.

Then there’s the backlog. Maybe I should have a big bonfire and be done with it. Except that my mental makeup won’t allow that. I need to sort through it and find *the important bits* and scan it all *just in case*. (I know. It’s just the way I am. Try living with me!). And, anyway, paper doesn’t burn very well, as I found a few years ago when I got rid of 20+ years’ worth of work notebooks that were a potential GDPR nightmare and just gathering dust.

A digital dilemma…

So, now my “paperless office” is getting closer. And I almost never print anything at work. But I create lots. Lots of documents that I write. Lots of photos that I create. Lots of digital files I download (instead of receiving printed copies) or scan (see above).

The trick is to make sure I don’t replace boxes and boxes of paper files with digital mayhem. A digital mess that’s spread across a variety of online services from Microsoft, Amazon, Dropbox, Google and Apple!

Advice and guidance is welcomed… comments are open below!

Featured image: author’s own.

Sorting a Word table of names, in Excel

This content is 3 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I’m going to share a secret with you all, dear readers. I really like Microsoft Excel.

There, I’ve said it. Excel may not be the perfect tool in some scenarios (take my example last year, which should almost certainly have used Power BI) but it is the “Swiss Army Knife” of the IT world. End users and IT admins alike have taken the humble spreadsheet programme and twisted it to suit their purposes. I can do so many things with it.

The problem – a table of names in Word

Like a few weeks ago, when I was reviewing a document. The document contained a table with a list of stakeholders. So as not to offend (in a very hierarchical organisation), it had been decided that the names should be in alphabetical order. Except the author had done that by first (given) name, not by last (family/surname) name. Rather than going back and asking them to fix it, I decided I would do so – as it should only take a few minutes.

Word tables are not always that easy to work with, but copying the data into a spreadsheet would let me mangle it, and then paste it back again. Incidentally, Visual Studio Code is also a fantastic text editor and I often use it for complex search and replace operations.

Moving the data to Excel for processing

In this case, I took the two columns from Word (Name, Job Title) and pasted them into Excel. That gives me something like this (massively simplified for the purposes of this blog post – I wouldn’t really go to this much effort for a three-line table! I also made up the names for this example…):

Mark WilsonPrincipal Architect
Brendan ClarkeDirector
Roger QuinnManager

Splitting first and last names

Next, I added a column to the right of Name, and split the Name column, using Excel’s Text to Column wizard and a space as a delimiter. That gave me separate columns for First Name and Last Name.

MarkWilsonPrincipal Architect
BrendanClarkeDirector
RogerQuinnManager

Sorting the data

I then sorted the data on Last Names:

BrendanClarkeDirector
RogerQuinnManager
MarkWilsonPrincipal Architect

Combining first and last names

Next, I used Excel’s Concatenate function to merge the sorted names back into a single field, for example:

=CONCATENATE(A1," ",B1)

The result is something like this:

BrendanClarkeBrendan ClarkeDirector
RogerQuinnRoger QuinnManager
MarkWilsonMark WilsonPrincipal Architect

Moving the processed data back to Word

Finally, I took the processed data and copied/pasted the combined Name and Job Title text back to Word.

NameJob Role
Brendan ClarkeDirector
Roger QuinnManager
Mark WilsonPrincipal Architect

Featured image by PublicDomainPictures from Pixabay.

10 years of Milton Keynes Geek Night

This content is 3 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

In June 2012, I joined a large group of people in a community arts centre at a converted bus station. It was the first “Milton Keynes Geek Night” and I really wish it had been me that had penned this tweet:

The History of Milton Keynes Geek Night

Inspired by Oxford Geek Night, which was upstairs in the Jericho Tavern (QI point: this was where Radiohead played their first gig), Richard Wiggins and David Hughes decided to create something similar for the Milton Keynes (MK) digital community. It took a while to get off the ground, not least because Richard and David were working in different spheres – one in London, the other in MK – but also because they were keen to find the right location. It shouldn’t be a pub, or a hotel conference suite, but something that better suited the event – and what better than a former nightclub above an abandoned bus station?

Aside from a location, Richard and David needed to find speakers. They wrote lists of people they knew, people they admired, people they thought would be good speakers, and the first two – Jon Hicks and Brendan Dawes – both said yes. Oxford Geek Night gave permission for their name to be taken (geek nights were a thing back then) and Milton Keynes Geek Night (MKGN) was born.

It has run on a quarterly basis for almost ten years now, plus an all-dayer (in 2014) and two Geek Mental Health Night specials.

“MKGN came out of a bit of frustration and self-desire to attend something locally with interesting creative and techy talks, with a bunch of like-minded people. I enjoyed heading to London, Oxford and sometimes much further afield to see and hear this kind of thing, but I had a feeling there would be enough interest locally in Milton Keynes. I obviously never expected or thought we would still be going 10 years later.”
[Richard Wiggins, co-founder, Milton Keynes Geek Night]

The Geek moniker

At this point, I should explain that to many of us associated with the term “geek” it is not an insult – but a badge of honour. One of dictionary.com’s many definitions for the word is “a digital-technology expert or enthusiast (a term of pride as self-reference, but often used disparagingly by others)”. Another is equally fitting: “a person who has excessive enthusiasm for and some expertise about a specialized subject or activity”.

Call me a nerd and you’ll get a very different response – but a geek is no bad thing.

With all that said, I’m still not sure how I, someone who can appear outwardly confident but is very uncomfortable in a room full of strangers, found himself at the first Milton Keynes Geek Night.

Beer and pizza

Maybe it was the beer and pizza that attracted me?

Playing to the geek trope, MKGN has traditionally had an association with these two forms of refreshment. Milton Keynes-based Domino’s Pizza were event sponsors for a long time, with a huge pile of pizza being delivered at the end of each event. And there is always a bar (with at least one free drink). In December 2015, MKGN 15 even had a specially commissioned beer from Verdant.

These things wouldn’t happen without sponsorship and MKGN has been fortunate to have been supported throughout its history. There have been too many individual sponsors to list here (large and small) but even the pizzas came out of a sponsorship idea (from Oxford’s JavaScript Conference, then Just Eat, and then Dominos). More recently, food has been sponsored by partners or ticket sales and provided by MK Gallery (the current venue).

The Talks

Throughout its existence, Milton Keynes Geek Night has kept a common agenda – two headline speakers, with a few lightning (5 minute) talks in between (although there have been some memorable occasions when speakers have pushed the definition of 5 minutes). And, until Covid-19 made passing a microphone between people problematic, there were also some “one-minute wonder” opportunities for people to pitch new employment opportunities, geek clubs, events and the like – but strictly nothing too commercial.

Over the years, MKGN has featured almost two hundred speakers. From the very first event, the calibre has been high – and many have made return visits, like Jon Hicks, Brendan Dawes, Christian Payne, PJ Evans and Ben Foxall. Many of the past events are listed on the MKGN website and I tried to pick some favourites, but there are just so many – from PJ Evans’ “Boiling Nemo” (MKGN 22) to Andy Taylor’s talk about the delivery robots that roam the streets of Milton Keynes (MKGN 40). But these just scrape the surface – the variety of the speakers and the topics they have spoken on is immense.

Throughout it all, there’s been a strong theme around web (now “digital”) design and development but there have been talks too that have covered other creative areas. It’s a format that has worked well for a decade now.

Richard and David have worked hard to deliver a diverse range of talks and speakers at MKGN – and are very conscious of how male-centric some events have become.

The speakers are still selected from a “black book” and the organisers considers themselves to be fortunate that their requests are rarely turned down (although sometimes people are, genuinely, just too busy). Commercial pitches are rejected (“that’s not what we do”, says David) but MKGN has also taken on speakers they haven’t previously heard of who have turned out to be interesting.

“We started off in the kind of digital space, I suppose, but we’ve always wanted to not be a tech or that kind of conference because “codey” stuff bores the **** out of me and we’ve always tried to find as wide a range speakers as we can – and we’ve been really lucky to do that.”
[David Hughes, co-founder, Milton Keynes Geek Night]

Evolution

For many years, MKGN’s home was “The Buszy”, run by Make a Difference CIC (MaD) in the former Milton Keynes Bus Station. In 2016, MaD had to move out and MKGN had to find a new location for its events too.

Around that time MK Gallery was developing a new venue – the Sky Room – with fantastic views across Campbell Park and the east of the town. The only problem was that it wouldn’t be ready in time for the next MKGN event. But MK Gallery did have a Project Space that could be used in the interim period. This became the home for MKGN until the Sky Room opened with its theatre-style seating in 2019.

MKGN has never been about profit – just covering costs – but, with the smaller audiences in the MK Gallery Project Space, it became necessary to charge for attendance (not least to put off those who may just have been attending for free beer and pizzas).

Then, came the pandemic and, in common with many other in-person events, Milton Keynes Geek Night was forced online for a while. But that had its advantages too – allowing MKGN to bring in international speakers that they could never have flown in for an in-person event.

Nowadays MKGN has returned to MK Gallery – although the theatre-style layout has given way to tables and chairs. This is to help social distancing but has a side effect that it’s brought the format full circle back to the days of the Buszy. Tables of small groups of people, brought together through a common interest in design, technology and creativity.

Social geeks

In the early days, I’d rush home and blog about the event. But an increasingly busy family life and decline in blogging meant I switched to live tweeting events. And now even Twitter seems to have fallen out of favour – with the #MKGN hashtag becoming much quieter than it once was.

Even if I was known as the event’s in-house blogger for a while, Milton Keynes Geek Night’s “official” channels are on its websiteTwitterInstagramSoundcloud (where recordings can be found for almost all of the talks), and a community on Orbit.

Recording the events

The first event had around 100 people but as audiences grew there was just too much interference and microphones started to struggle. After the technical challenges that the second MK Geek Night suffered, Richard was researching options for new audio equipment when Andy Hagon and Roo Machell said “we can help”. Since then, every MKGN has had professional sound mixing and recording, first using Andy and Roo’s own mixing equipment and later using MK Gallery’s desk and MKGN’s own microphones.

Community

Both Richard and David speak of seeing the friendships that have developed through Geek Night as a massive positive outcome.

“It’s about bringing people together and letting them hear talks that they probably never would otherwise. I love that so many friendships have occurred over the years at MKGN and I’m lucky to have personally made many good friends too.”
[Richard Wiggins, co-founder, Milton Keynes Geek Night]

Speaking personally, I met both Richard and David through the event (later discovering that we had other shared interests) but there have been many, many more people who have met through MKGN. David explained to me that he started to notice the same groups of people at the same tables each event. And he could see those friendships, networks, bonds, etc. starting to grow. MKGN had become a community.

Over time, the MK Geek Night audience has changed. Some of “the Oxford posse” still come along whilst others have moved away. But Milton Keynes has changed in the last ten years too. It’s become a bit of a tech hub – and that brings new businesses and new people. MKGN still has its “regulars”, but the audience has expanded, and new attendees have brought their own groups. All this change has been positive – and is part of the evolution of the event and the community that surrounds it.

I asked some of the attendees for their thoughts and it was fantastic to hear the warmth and enthusiasm that they have for Geek Night:

“The biggest thing is the people. It’s a social event. It’s meeting up with these guys. We became friends from Geek Night. And the talks are amazing […] what I come away with every time is always challenged, always enlightened in some way.”

Others spoke of how they are educated and informed by the speakers. One even told me how she is not a geek in the traditional sense – she is a musician – but can take things from the geek talks and adapt them to her profession.

“So, it’s adaptability as well. It’s not just about coding.”

Another told me about his need to research the things he’s heard about at Geek Night and discover more:

“I would say my typical geek night experience is that I come back from the events with a list of things I want to Google in my head. And I will be up late tonight, researching all these new fantastic things that I’ve been exposed to.”

What’s next for MKGN?

MK Geek Night’s tenth birthday is coming up on 16 June 2022 and tickets are already sold out. I asked Richard about future plans and it’s clear that there are some big ideas around exploring new formats and keeping the event evolving.

September 2022’s MKGN will be a 2-day creative industries and digital technology conference called All Flows. But, from talking to Richard about the project, a conference is just not the right word. The All Flows team (which also includes Pooleyville’s Simon Wright) are looking to create more of a festival vibe which extends into the evening, including inspirational talks, music, and more. The exact format is still work in progress, but Richard shared with me some of the speakers that are lined up and it sounds impressive. All Flows will take place on 29 and 30 September 2022 although Richard is hoping it will go on to become a regular occurrence – a sort of “Geek Night on steroids”.

Meanwhile, Richard and David are exploring options for the December 2022 MKGN – and whilst I don’t want to let the cat out of the bag, some themed events may be on the cards.

It seems that the Milton Keynes Geek Night team have hit on something special. Back in 2012, Richard and David never expected MKGN to run for ten years but the constant evolution brought by changes in venue, a global pandemic, and a desire to stay fresh have become part of the recipe. Add to that a thriving community and MK Geek Night must be seen as one of Milton Keynes’ great successes.

Featured image from the MK Geek Night Instagram feed.

This post was originally published (with some great photography) at Pooleyville.city.

Step back from the problem and think about what “good” looks like

This content is 3 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A few weeks ago, I sat down with a Chief Information Officer (CIO) who has a problem. He’s in the middle of a messy “divorce” (professionally, not personally). He is transitioning services from a shared services agreement with another public sector body to a new managed service. His own organisation’s IT maturity is low. There’s an expectation that the new managed services partner will take on everything (except it’s not in a state that is ready to take on). And the shared service provider is both making transition difficult (preserving its revenue stream) whilst ramping up the price to carry on providing services. The divorce metaphor is very apt. 

I was brought in (alongside a colleague with relevant sector experience) to help smooth the pain. I needed to understand what’s holding up the process – why is it so difficult to provide basic information for the managed services provider to take on the service? What are the gaps? How quickly can they be filled? And what is needed to move to the next stage? 

It’s not my usual role, but I’ve been around this industry long enough to be able to take a step back, look at the problem, and try to work out what “good” looks like. 

The challenges

The CIO presented me with two challenges: 

  1. Visibility – of what’s happening. What will be done by when and how far off the target is the transition?
  2. Passiveness – don’t just sit and wait. Bang down some doors and ask for information. If it’s not forthcoming, then flag it. There is no time for delays. 

Searching for a solution

The next day, I was mulling over the issues and I bumped into a friend (on the market in the town where we both live). We went for a coffee, and I told him about my problem (without compromising any confidentiality). My friend has a military background, followed by IT Service Operations and, more recently, security (he’s a Chief Information Security Officer – CISO) so I shouldn’t have been surprised by the advice he gave me. The way he saw it was that there are a bunch of service transition “packages” but the business as usual (BAU) service isn’t complete. Meanwhile the CIO has no visibility and would like to see where things are and the plan for where they will be.

After our conversation my mind was clear. I needed a way to track progress. I wanted a dashboard to tell me the state of each service component or process. Then, the applications, servers and other infrastructure could fall in beneath – but I needed to know there is a service to transition them into. 

There are many problems with dashboards (though the etymology of the word is about protecting riders on carriages from what might be thrown at them from below… so maybe that’s quite appropriate after all). Red/Amber/Green (RAG) statuses can be problematic too (both for cultural reasons and because of accessibility, although that can be overcome with appropriate design). But I didn’t want perfect – I needed functional. At least for the first iteration.

The chosen approach

The Microsoft-focused Solution Architect in me was thinking Power BI but I lacked the skills, time and access to licenses. I needed something that could be developed quickly and updated easily. My initial PowerPoint deck with, “this is what we said we would do”, “This is where we are today” and lots of red, turning amber then green was quickly pushed aside by a colleague in favour of Excel. In fairness, the world runs on Excel – and that’s not necessarily a bad thing. With the addition of a few formulae, some data validation and some conditionally formatted cells, we soon had a dynamic report. It highlights missing information. It highlights support status. It highlights key dates (and missed dates – because I’m also realistic).

Answering the exam question

The summary sheet should answer the CIO’s visibility issue (once it’s securely shared) and constantly pushing for the detail should strike out any perceived inactivity or a lack of initiative.

It’s not innovative, but it is elegant. And it works. 

So I have the tech in place – now for the difficult bit (the part that involves people) – dragging out the missing information to turn cells from red to amber to green. And the good thing is that, based on a meeting yesterday, it feels like there are a bunch of people in the managed services organisation that can see the value and are invested in the solution (they are even adding sheets for extra information – like tracking risks, issues and dependencies). That’s half the battle. “All” I need now is to get the various projects that hold the information on the various applications, servers, etc. to join in.

I may return to this subject with an updated post when everything goes live. Or I may not, for commercial reasons, but here goes… wish me luck! 

Featured image: author’s own.

Not-so-helpful social media “help”

This content is 3 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Social media is big business. And almost every major business to consumer (B2C) organisation has at least one account on each of the major social media platforms (at the time of writing, that’s Twitter, Facebook and Instagram but I’m sure it will change over time). 

Unfortunately, there’s a concerning trend starting to emerge – one where the “conversation” is moved to control the brand image. Many brands have set up <brandname>Help accounts for their customer service so that the main brand account is “clean” – pure marketing, untarnished by customers expressing concern about the products and services. Meanwhile, the “Help” account may be operated by a communications agency, simply offering a face and redirecting customers to other channels. 

And that’s where the problem lies. If you want to offer omnichannel support, then you need to meet your customers where they contact you. It’s no good offering “help” on Twitter when all you’re really doing is advising customers to phone your contact centre. That does not help. That’s obfuscation. It’s a blatant attempt to preserve the online image of the brand, whilst offering shoddy customer service. 

So, here’s my plea to brand managers across the UK. If you offer a <brandname>Help account, then make sure it provides real assistance and is not just signposting to another channel. 

I’ll provide an example here, from @KwikFitCS (who responded to my tweet for the main KwikFit account… more on that in a moment), but they are not alone…

Then there’s the issue of the information that <brandname>Help accounts ask for to verify you before they will provide help…

In the example above, @BootsHelp replied to a tweet sent to @BootsUK. And the issue I was reporting was a website problem that was not specific to a single account – the web team could investigate without my personal details. Maybe I should be the one looking for the verification here… not them? That may sound a bit extreme but what’s to stop anyone from setting up a spoof <brandname>Help account and harvesting information from disgruntled customers? (In fairness, the @BootsHelp account has been verified by Twitter, but the @KwikFitCS example earlier was not).

And Boots is not alone – here’s another example from @Morrisons, the UK supermarket chain:

The request went on to a second tweet:

So, come on B2C Twitter. You can do better than this. How about providing some real help from your social media channels? Preferably without requiring a long list of personal details.

Featured image by Biljana Jovanovic from Pixabay.

Failure Demand in action

This content is 3 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Recently, my work has involved some analysis of a local authority’s business processes. As part of that I’ve been thinking quite a lot about the concept of “Failure Demand”. For those who are unfamiliar with it, Failure Demand is described by the occupational psychologist and author John Seddon as:

“It is demand caused by a failure to do something or do something right for the customer. Customers come back, making further demands, unnecessarily consuming the organisation’s resources because the service they receive is ineffective. ”

Failure Demand – Vanguard Consulting Ltd

Whilst the Vanguard page is worth a read, there’s another great example of Failure Demand in the “How to break the first rule of Systems Thinking” post from ThinkPurpose.

What does Failure Demand mean in practice?

Any system used to provide a service has a given capacity. To use this efficiently, there is a balance between reducing resources and managing demand.

On the resource side, we can look at how resources are used:

  • Do we have the right people and skills?
  • Are they motivated and focused?
  • Are processes efficient?
  • How is IT used?
  • Can self-service help?

When it comes to demand, the first question to ask is not be how effective the use of resources is. We should really ask are they doing the right thing? Does it meet the customer need?

If it doesn’t then there will be repeat contacts, often relating to Failure Demand – where the volume of work is increased by managing incidents of failure within a process. Examples of Failure Demand include “you’ve sent the wrong item” or “the person didn’t meet the agreed appointment”.

It often takes longer to put something right than to get it right first time. An organisation can implement the very best systems but if it doesn’t meet customer needs in will fail. This is true whether that customer is internal or external; paying for a service or not; client, citizen, traditional “customer” or student. Customers will become frustrated and annoyed that they have repeated contacts to avoid issues. Staff suffer reduced morale as a result of their increased workload.

A real world story of Failure Demand

I spent a good chunk of one day last week working from a car dealership. It doesn’t matter which one… this could have been one of many up and down the country. I also know they are really hot on customer satisfaction. I’d like to make it clear that all of the staff involved were friendly, attentive and did their level best to help me. This is not a complaint, just a true story that helps me explain the Failure Demand concept.

My car is 3 years old, so it was booked in for a service, statutory MOT test, warranty checks, and a quote for an extended warranty.

As the day went on, I saw the Service Manager getting more and more stressed. He wants to do the best he can for his customers but the team is down from 4 to 2 at the moment. That’s going to be tough, but then we layer on the Failure Demand.

At 12:30, my car was nearly ready (it just needed cleaning) and I paid the bill. That was proactive, working to close my account and get me on my way. Great customer service, nothing so far to detract from the outstanding feedback that the dealer hopes to receive (maybe I’ll come back to that in another post).

But I asked about the warranty quote I had requested a week earlier. The person who could deal with that was off work (for understandable personal reasons) but the receptionist who had booked my appointment had assured me it would not be a problem. so, a message was left and someone will call me back after the weekend (Failure Demand 1).

At 13:30, I chased up to see why I was still waiting for my car. It hadn’t been cleaned (Failure Demand 2).

At around 14:00, I got my car back. The service handbook had been stamped and details added for the third service but the second was blank. I always take my car to this dealer, so it must have been missed last year. So the Service Manager looked up the details and added them to the book (Failure Demand 3), once he had found his stamping machine.

By now, I was embarrassed that I kept on going back with “things to fix” and I drove away. As I left, I found that my seat was in the wrong position, the dashboard display was unfamiliar, the doors automatically locked (and much more besides). The profile settings associated with my key were missing!

Having heard the receptionist fielding calls to try and let the Service Department focus on customers who were already in the building, I knew that phoning would not get me an answer any time soon. So, I returned to the dealership to see if the settings were lost for good, or backed up somewhere (Failure Demand 4).

Another Service Manager confirmed that they are not backed up. Some software updates are non-destructive. Others less so. So I left again, disappointed.

Except, as I started the car, my seat moved itself, the dashboard was set up as I expected! My profile had loaded but, presumably the software update had been incomplete before. Now it had finished, everything was back (phew).

Later that day, I received a text message. It contained a link to the video report of the inspection on my car during the service. Nice to have, except I’d authorised the repairs hours previously. Not exactly Failure Demand, but potentially another issue to fix in the process.

So, what’s the answer?

The intention is to move to a position where available system capacity is focused on “Value Demand”. Value Demand is characterised with things that deliver value to the customer or to the organisation, such as provision of information, or just getting it right first time.

If the warranty quote was ready when I paid the bill, the car had been washed when I was told it would be, and the service handbook had been stamped first time then I would have been happy and three items of failure demand could have been avoided. If the Service Manager had known to tell me that software updates might still be taking effect when the car was restarted I might have been less concerned about the missing profile.

The customer would have been happier, the Service Department’s workload would have been lower, and the Service Manager would have been less stressed.

It seems that spotting these issues as a customer is easy… the trick is working out how to fix them in my own work processes…

Featured image: author’s own.