Let me share a recent experience with you….
A young IT auditor texted me at work and asked for some Active Directory user account data that I capture automatically every week, using some scheduled ACL scripts.
He wanted to add user names, cost center, and other user account data to the list of user IDs he had obtained from his client.
I directed the auditor to the location of the data, to which the entire audit department has access.
Step 1: Map the Drive
The auditor (let’s call him Jamie) texted me again a short time later, saying he could not find it (imagine that, an IT auditor with 2 years of ‘experience’ who can’t map a shared drive [see my list of What IT Auditors Ought to Know – and Don’t!]).
I walked over to his desk and showed him how to connect to the drive.
Step 2: Identify a Problem
After he opened the data I captured and saved a copy of it, I asked to see the user list that he had obtained from the client.
I noticed that his user list included some user IDs that contained extra characters at the end, which (in this company) indicate the ID is a special ID and has increased access (don’t get me going on this ridiculous scheme, that’s another story).
This particular company uses the first 3 letters of the user’s last name and 3 numbers to create user IDs, such as JON316.
However, for special IDs, this company uses an ‘x’ after the ID refers to the first additional security level, ‘y’ to the second, ‘z’ to the third.
So a gal named Bella Jonah is assigned JON316 for her everyday account and JON316x and JON316y for her special accounts.
The data that I capture each week contains only basic user IDs, like JON316; no x, y, or z accounts.
Step 3: Transform the Data
I mentioned this difference to Jamie, and told him that he’d need to use an Excel formula to remove the extra character (x, y, z) at the end of his list of user IDs so that they can be matched to the data he received from me.
“Have you used the LEFT formula in Excel? Just keep the first 6 characters?”, I prompted.
“Put your cursor in cell D1 and type =LEFT(A1, 6) and press ENTER. Got it?” I searched his face for an answer.
“Uh, sure,” came back the reply. The words didn’t match the face expression.
“Just copy it down the column. Will you remember to use that formula in the future?” I asked.
“I’m not familiar with Excel formulas. Still learning them.”
(Did I mention this auditor had 2 years of experience?)
Step 4: Eliminate Manual Labor
“So you know how to use vLookup* to match data, but you’re not familiar with the LEFT formula? So how were you going to trim those user accounts in your data?,” I asked.
*I had heard that whenever Jamie used vLookup, he asked another auditor to help get the formula right.
“I was going to clean the data manually,” Jamie said, almost defiantly.
“All 150 accounts? By hand?” I questioned. “Really?”
“Yes.” Silence. Blank stare. Not any trace of guilt or shame.
Look Mom, No Formulas!
“Then let’s forget Excel formulas,” I said, recovering. ” I’ll show you an easier way, no formula involved,” I directed. “Position your cursor in the next blank column, cell E1.”
“Ever heard of Flash Fill?” I asked (I’d written a couple articles on the department Sharepoint site about Flash Fill and had demonstrated how to use it in a previous department meeting. One that this auditor had attended.).
See my post about Flash Fill. If you don’t know it, don’t miss this one. Flash Fill can do so very much.
“No,” the auditor replied swiftly.
“With your cursor in cell E1,” I said, “Note that the user ID is cell A1 is JON316x. Type what you want to appear in cell E1–JON316.”
The auditor typed the user ID flawlessly.
“With your cursor in cell E2, note that the user ID in A2 is ROM323z. Type what you want to appear in cell E2–ROM323.”
Again, flawless typing.
“OK, now put your cursor in cell E3, click on the Data tab, then click the Flash Fill button.”
“It finished the entire column!,” the auditor exclaimed. “Wow!”
“Now go back and click inside cell E1. See a formula?”
“No, I don’t. That’s cool!” the auditor admitted.
“I hope that changes your life,” I said, turning to exit his cube.
Questions for You
- What would have been the result if I had not noticed the x, y, z characters added to the end of some of Jamie’s IDs and he successfully performed a vlookup?
- Editing data manually takes a lot of time. What is the other major problem with Jamie’s’ manual solution?
Please comment below, and we’ll discuss.