Assistance With a Task

So my boss has given me the task of looking at two spreadsheets, one being a spreadsheet of our ActiveDirectory containing all employees, and another containing the LastPass details of our employees.
My boss wants me to see which employees have lastpass account, and which do not, and create a column that will indicate "Y/N".
Any advice to avoid any needless busywork when I can just functions/automation?
So one conundrum is that the lastpass spreadsheet has employee names formatted as:
[[email protected]] as one cell
while the Active Directory spreadsheet has:
[Lastname, Firstname] as one cell.
Fortunately I've split the active directory column into two columns being:
[Lastname] [Firstname].
Can someone who is proficient at excel throw me some ideas as to how I may make it so I can see who does not have last pass yet?
The Active Directory sheet is the one that has all our employees so it is the primary one I will be using. On the Lastpass spreadsheet is everyone who has one. Basically I need to find out who has an entry on ActiveDirectory but does not exist on Lastpass spreadsheet.
Unfortunately ActiveDirectory does not store their [email protected] email.
How do I find the ActiveDirectory users that have no counterparts in the LastPass Spreadsheet?

Other urls found in this thread:

exceltrick.com/formulas_macros/vlookup-in-excel/
stackoverflow.com/questions/16555816/i-want-to-compare-two-lists-in-different-worksheets-in-excel-to-locate-any-dupli
superuser.com/questions/541260/find-alike-data-in-two-worksheets-and-put-in-3rd-worksheet
ablebits.com/office-addins-blog/2015/08/26/excel-compare-two-columns-matches-differences/
ablebits.com/office-addins-blog/2016/02/25/compare-two-excel-files-sheets/
twitter.com/SFWRedditGifs

Looking at the spreadsheet, how would you know if a person has a Lastpass or not?

give us a dummy example

Don't wanna divulge company information so cannot provide screen caps, however:
there are two seperate spreadsheets.
1 is Active Directory. 2 is LastPass.
If an entry exists in 2, then they have a last pass.
Basically, everyone in 2 has an entry in 1, but not everyone in 1 has an entry in 2. My task is to find out who in 1 lacks an entry in 2.

Doing some more research I came across the Excel function "V Look Up", can anyone elaborate on it?

fuck, that'd be so easy to do in sql

do you even know how to program something in any language?

I once made a "play button" in middle school for a newgrounds flash.

Oh right, I remembered you from yesterday now

did you look at this page? exceltrick.com/formulas_macros/vlookup-in-excel/

I did find a few pages about the function, but I do not know exactly what I need to do to manipulate it to do what I'm setting out to do.

stackoverflow.com/questions/16555816/i-want-to-compare-two-lists-in-different-worksheets-in-excel-to-locate-any-dupli

superuser.com/questions/541260/find-alike-data-in-two-worksheets-and-put-in-3rd-worksheet

ablebits.com/office-addins-blog/2015/08/26/excel-compare-two-columns-matches-differences/

ablebits.com/office-addins-blog/2016/02/25/compare-two-excel-files-sheets/

Thank you.

you're welcome

Is this what it's like to not know how to program? Not knowing how to solve such a trivial problem? How do you survive? I apologize for sounding like a douche but I'm genuinely curious.

not him, but it looks like it

I personally don't know how to make vb macros, would try to niggerrig a sql query to do that

Yeah, I'm pretty good with hardware, and have other skills not related to my career path, but as someone majoring in IT I really hope that my courseload teaches me light/practical programming.

Why not just go to codecademy or something? Learning enough programming to automate everyday tasks is literally a few hours of studying.

Every day tasks like what?
Not trying to sound condescending either, but what.
I'd rather spend my time reading books, working out, cooking, practicing boxing, etc.

>Every day tasks like what?
Like the problem you're having right now obviously. Do you think this is a unique one time thing? Once you know how to do it, applications will obviously appear elsewhere as well, in places where an automated solution didn't even occur to you previously.

well, for example I made a Sup Forums image downloader, it's easier than downloading one image at a time or fucking around with downloading the entire page and deleting thumbnails

>Its another episode of please do my work for me Sup Forums also i can't provide any example at all because that would involve me doing work at all

woah you made this?

lol no

I made a c# program, not a browser extension

>Using lastpass rather than a enterprise class solution like ManageEngine Password Manager Pro
>Using Excel rather than SQL
>Please literally do my job for me
how incompetent are you?

>cooking
This kills the man.

literally only the last one is his fault

>literally only the last one is his fault
He could have imported the excel file in to a SQL table. And he could have told his boss to stop being a faggot who trusted all his passwords to the cloud.

superuser.com/questions/541260/find-alike-data-in-two-worksheets-and-put-in-3rd-worksheet
Is the most promising, but it seems like everything only compares one column with another, when I need to compare 2 columns with 2 columns (both of the columns being [firstname][lastname]).
Internship man.

>Pissing away tens of thousands of dollars
>Money you dont even have, just debt
>Not learning how to even use Excel let alone SQL
Thank you for proving again that the only thing a degree demonstrates is that you're piss poor with managing money, and that you cant teach yourself anything without having someone hold your hand.

Ah interesting.

>and that you cant teach yourself anything without having someone hold your hand.
case in point, you had to ask Sup Forums to hold your hand to google your question for you. what is your major OP? let me guess, CS?

IT :^)

What the fuck do they teach you?

High school?

Do you want to see how easy this is in SQL?

select AD.firstName, AD.lastName
from ActiveDirectory as AD
left outer join KeyPass as KP on AD.firstName + '.' + AD.lastName + '@work.com' = KeyPass.email
where KeyPass.email is not null

>select AD.firstName, AD.lastName
>from ActiveDirectory as AD
>left outer join KeyPass as KP on AD.firstName + '.' + AD.lastName + '@work.com' = KeyPass.email
>where KeyPass.email is not null

and alternatively

select AD.firstName, AD.lastName
from ActiveDirectory as AD
where not exists (select * from KeyPass as KP where KP.email = AD.firstName + '.' + AD.lastName + '@work.com')

but he wants all employees, with a "yes" or "no" column

i've been up all night, sue me


select AD.firstName, AD.lastName, 'Yes' as 'Has KeyPass'
from ActiveDirectory as AD
left outer join KeyPass as KP on AD.firstName + '.' + AD.lastName + '@work.com' = KeyPass.email
where KeyPass.email is not null

union

select AD.firstName, AD.lastName, 'No' as 'Has KeyPass'
from ActiveDirectory as AD
left outer join KeyPass as KP on AD.firstName + '.' + AD.lastName + '@work.com' = KeyPass.email
where KeyPass.email is null

there are more elegant ways but im lazy and rarely write sql

>select AD.firstName, AD.lastName, 'Yes' as 'Has KeyPass'
>from ActiveDirectory as AD
>left outer join KeyPass as KP on AD.firstName + '.' + AD.lastName + '@work.com' = KeyPass.email
>where KeyPass.email is not null

select AD.firstName, AD.lastName, case KeyPass.email is not null then 'Yes' else 'No' end as 'Has KeyPass'
from ActiveDirectory as AD
left outer join KeyPass as KP on AD.firstName + '.' + AD.lastName + '@work.com' = KeyPass.email

Looks like French to me, but I admire your knowledge.

I've been looking at these guides for about an hour or two and I'm still just looking at my spreadsheet where Sheet1 is [firstname][lastname] of AD and where Sheet2 is [firstname][lastname] of LastPass.
I think it being two columns complicate things. I also can't just use one column because there are duplicates of firstnames and lastnames.

>I also can't just use one column because there are duplicates of firstnames and lastnames.
in SQL then you use select distinct to remove duplicates from a result set

How do I use SQL.
All I """know""" to use is Excel.

>All I """know""" to use is Excel.
If you can't do this in excel then you clearly dont know how to use it

>How do I use SQL.
Install MS SQL Express Edition (since you dont want to cause licensing issues at work and get fired). Open SQL Server Management Studio, open the node for your SQL Server, right click on the Management folder and select Import Data

1/2

Under Data Source select Microsoft Excel
click next a few times to import them in to a database you would have created before hand which is just as easy

Alright got tired of this complicated bullshit.

I just combined firstname and lastname into one column for both sets of data so I only have two columns, then I just used Conditional Formatting --> Highlight Duplicate values.

Now I have a list of names of people in active directory who do not have last pass, time to google how to make it so I don't have to manually input Y or N in a column next to their name to denote they do not have lastpass.

that's what primary keys are for
eg.: ID

isn't there an ID column where the id is unique for each person?

For AD yes, for LP no.

Also tell your boss he is a retard because email addresses are stored in the mail attribute of the user object in active directory, unless your not only not uses exchange, but is too lazy to manually populate them so it integrates with outlook.