Useful Excel Macros #1–compare two columns

I often need to compare two columns, and get a list in a third column of the items that are in one column, but not the other.

Every solution I find online has one common problem – the third column is full of blanks in between the items. I don’t want blanks. I want items.

So I wrote this function, which returns an array of the missing items – items which are in the first parameter, but not in the second.

I’m probably missing a trick or two (I’m particularly not happy with the extra element in the array that has to be deleted before the end), so please feel free to add to this in the comments.

Public Function Missing(ByRef l_ As Range, ByRef r_ As Range) As Variant()
' Returns a list of the items which are in l_ but not in r_
' Note that you need to put this formula into a range of cells as an array formula.
' So select a range, then type =Missing($A:$A,$B:$B), and press Ctrl-Shift-Enter
' If the range is too big, you'll get lots of N/A cells
Dim i As Long ' loop through l_
Dim l_value As Variant ' current value in l_
Dim y() As Variant ' Temp array to store values found
ReDim y(0)

For i = 1 To l_.Count ' Loop through input

  l_value = l_.Cells(i, 1) ' Get current value
  
  If Len(l_value) = 0 Then ' Exit when current value is empty
    GoTo exitloop
  End If

  If r_.Find(l_value) Is Nothing Then ' Can't find current value => add it to the missing
    ReDim Preserve y(UBound(y) + 1) ' Change array size
    y(UBound(y) - 1) = l_value ' Add current value to end
  End If
Next i
exitloop:
If UBound(y) < 1 Then
  Return
End If
ReDim Preserve y(UBound(y) - 1)
If Application.Caller.Rows.Count > 1 Then ' If we were called from a vertical selection
  Missing = Application.Transpose(y) ' Transpose the array to a vertical mode.
Else
  Missing = y ' otherwise just return the array horizontally.
End If
End Function



.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Lenovo experience

I have been a Lenovo customer for many years at home, in my home business and at work (until recently, when my employer switched to using Dells). I had switched to Dell for my last laptop, and was not impressed with the machine’s durability, power, or support policies. So with my most recent purchase, when the Dell started intermittently failing, I switched back to buying from Lenovo.


In June of 2013, I bought a Lenovo Thinkpad T530. Aside from some disappointment at not being able to get the same power and durability with a touch-screen, I settled down to enjoying my new laptop.


On Friday July 19, 2013, I started a two week vacation, and brought my laptop along so I could keep up with my home business’ emails, as well as keeping my phone synced and family entertained with videos. We started with a six-day drive, during which the laptop appeared to be working fine, except for on two occasions when I took the laptop out of my bag to find it warm and powered on. I am always fastidious about turning off or suspending my laptop before putting it into the bag, so I was surprised the first time this happened – and subsequently had my family witness me shutting down the laptop before putting it into the bag, only to find it had turned on again when we reached one of our destinations.


I researched this on the Internet briefly (I’m a Microsoft MVP, so if there’s information out there, I’m usually able to find it), but didn’t find anything of any consequence that suggested this was either a widely known problem or a significant issue. I resolved to call Lenovo tech support on my return.


We arrived at our destination hotel on Wednesday July 24, 2013. As is usual, I plugged in my laptop and used it to monitor my email, etc. Later that evening, I returned to my laptop to find that it had turned off. I tried to turn it on again, but while the power button and webcam light both flashed on, the laptop didn’t boot. Being too late to call technical support, and with the next two days fully scheduled with my family for a vacation, I was unable to call until the weekend.


On Saturday July 27th 2013, I called Lenovo technical support. Despite the IVR telling me that I was being connected to Atlanta Georgia, the accent of the person who answered the phone was definitely not a Georgia local. I’ve lived in a number of different parts of the world (I’m an immigrant to the US myself), and my job puts me in touch with many people who have strong accents, so I was hugely irritated to find that I could not understand the person to whom I was speaking, and that he could clearly not understand me. Despite this, I tried to explain my problem to him, and to ask for a cross-shipment of replacement components or a full system, into which I could swap my hard drive on my return home and be up and working immediately. This has been my experience with previous Lenovo support issues – that I can get my replacement sent to me, so that I am without my computer for as little time as possible.


The diagnostic approach taken by this technician was minimal, and basically consisted of checking that I had tried to boot my system on AC power as well as from the battery. He then spent some time telling me that I needed a new system board (I can replace most system boards), but that he wouldn’t ship one to me, I had to ship my entire system back to IBM. He also told me that if I didn’t like this, I could apply to become an IBM Business Partner and buy parts to replace them in my own system.


Disappointed, I asked to speak to a supervisor, and he assured me his supervisor was “on a break”. Could the supervisor call me back? “No, we are not allowed outgoing calls”. What about your supervisor’s supervisor? “He is also on a break”. Unless this guy sits right next to the break room and observes everyone going in and out, the speed of his response leads me to believe that either he has been told never to put calls through to a supervisor, or he avoids doing so in the belief that this will reflect negatively upon him.


Assuming that I had somehow got put through to “second string support” because I called on a Saturday, I asked the technician to escalate my case to a supervisor, which he said he would do. A thirty-five minute call of pure frustration culminated in the technician’s inability to understand me in the slightest as I realized he had completely butchered my name – I know the first name is a little unusual, but “Jones” is surely common enough that he can’t get it wrong. Sadly, no, he keeps calling me “Mr Johnses” despite my spelling my name and correcting him at least twice.


Monday came and went without a call from a supervisor.


On Tuesday July 30, 2013, I called again, and this time was able to understand the technician far better. I explained to him my problems with the first technician, and asked that they correct my name, and confirmed again that IBM will not cross-ship parts or system to allow me to resume operations immediately on my return home. A little over ten minutes later, still not happy with what is being offered, I agree that they can ship an empty box to my house, so that I can ship my system to IBM for investigation and repair / replacement, in “up to seven business days”. At several points during this phone call, I try to explain that this has not been my experience of IBM / Lenovo support in the past, but each time I try to raise my concerns, the technician interrupts me and will not let me finish what I am saying, leaving me feeling just as frustrated as with the first technician, even though I am at least able to understand this one.


I finally ask him to escalate me to a supervisor, which he agrees to do. He connects my call out to a system that assures me every thirty seconds or so that I will be dealt with shortly. Given that there is only silence between these sentences, so I can’t be sure I haven’t been disconnected, I put the phone on speaker, so my wife (a former tech lead at a support company) can hear how Lenovo’s systems stink, and after a few repetitions of a brief assurance that I will be answered shortly, the system finally tells me it is unable to complete the connection, and that I should dial “the 1-800 number”. Then it disconnects, leaving me with no idea of WHICH 1-800 number I should call to get reconnected, to escalate my issue, to get any kind of ability to register my concerns with Lenovo about the lousy quality of their support.


At this point, I have given up on Lenovo phone support, because it seems clear that it is as awful as Dell’s. Given that my laptop malfunctioned within two months of its purchase, I start to believe that I made a mistake returning to Lenovo, thinking that I would get better treatment and sturdier systems than I had when purchasing a cheaper system from Dell. This is why I reached out to @LenovoHelp, because I hoped someone at Lenovo still cared about the company’s reputation, and could do something to make this good.


To add insult to injury, when I finally returned home late on Thursday August 1, I find an empty box sitting outside my house. It is addressed to “ALAIN JOHNSES”. Since this is not my name, it means that I can’t request the laptop return be re-addressed to me at work, because my work will be unable to find me using that name. When the repaired system is returned to me, UPS will refuse to deliver it to an empty house, and I will have to schedule more time off work to go and pick it up. I hope they don’t ask for ID, because that won’t match, because that’s NOT MY NAME. Nonetheless, I shipped my laptop (minus the hard drive) on Friday August 2, so as to get it back soonest.


What do I want Lenovo to do to address this and make good on their failure to provide adequate service? It’s clearly too late to make this process happen quicker, that’s already failed completely. Here are some suggestions:


1. Ensure that phone technicians are comprehensible. The first technician had such a thick accent he should never have passed an interview for a phone job in English.


2. Train technicians on customer handling. You do not interrupt the customer, because that irritates the customer. You let the customer know what you can and can’t do for them, so they don’t have to fish around. You accept escalations to supervisors because that’s the only way to handle customers who want to talk about the quality of service they’ve received.


3. Cross-ship, even if you have to put the replacement items on the customer’s credit card until you receive the damaged item. I would have been happy to do this, as I have in the past. This was a distinguishing feature of Lenovo’s service in the past.


4. Make sure that if you offer weekend tech support, it is not staffed with the “second string”.


5. Systems used for escalating customer complaints must allow the customer to disconnect and call back, or be called back, at some later time. Twice I asked to be escalated, and in each case, but in different ways, I was denied the opportunity of speaking to a supervisor. Also, Lenovo was denied a chance of explaining their side, of making me less unhappy as a customer. Successful escalations are a good thing for the customer and the company, so technicians should be trained not to sidestep them by insisting that their supervisors are all “on break”.


Please make me believe that I made the right choice in switching back to Lenovo. Right now, I don’t feel happy with my purchase.

I cry every time I see a password prompt

Every company I’ve worked at, there’s been at least a couple of enterprise-wide sites that ask me to log on, and they prompt me for a user name and a password.


Clearly, if I’m connecting to an intranet site, what I need that server to do is to implement Kerberos to recognise me. That’s a great feature of modern web browsers, and it’s relatively easy to do in multiple web server frameworks. I’ll leave that as an exercise for the reader.


But that only works for internal sites, where the server and the client can each communicate to the Kerberos servers responsible for authenticating server and user. Maybe I’ll talk about that some other day.


Today, I talk about achieving SSO through Federated Identity. I’m going to start by talking about the theory.


Why?


What’s the benefit?


Simply put, security is improved dramatically, and administration and user effort is reduced.


Security benefits


No reuse of passwords – because even though we tell you not to use the same password across multiple sites, it’s well known that everyone does.


The user experience


How does it appear to the user? Simple – as long as they are logged on to the corporate network, they browse to a remote site affiliated with the company, and after a couple of brief redirects, they’re automatically logged in to the remote site with credentials and access that matches who they are.


OK, that’s great – far better than having to use and remember a different password for those remote sites.