>So Anon, it says on your CV that you have 'advanced' Excel skills
>I'll give you a warm-up problem to get you started
>Say I have two lists of 10,000 customer emails, how would I ensure that no emails from list A are on list B?
Throw both excel docs in MS Access and do a simple dup check query in sequel.
>>1126329
>using Access to perform a simple excel operation
>>1126339
Fuck off, if you paid you a MS licence, you get Excel and Access together anyway so there isn't any real argument to be made against it.
Are you saying you don't know SQL?
Shits gonna get tough for you when the 'advanced' part sets in.
>>1126344
You can just do a vlookup or make 1 list and eliminate duplicates, make 1 list and pivot it, etc it would be fucking retarded to open another program to do this.
>>1126344
>Are you saying you don't know SQL?
I'm saying that there's a built-in way of doing this in Excel, Anon.
You could do it using SQL, Python, or whatever you like, but there's a much simpler excel way to do this
>Shits gonna get tough for you when the 'advanced' part sets in.
I'm not the one using Access for this :^)
>>1126351
This guy knows what's up
>>1126351
>another program
What other program?
>>1126344
You just put them all in column A then press the remove duplicates button. I guess use upper as well if you're feeling very thorough. You can do it in the time it takes for Access to load.
>>1126358
Never said to combine the lists, just to ensure that nothing on list A is also on list B.
Vlookup is the best way to do this
>>1126361
Then just highlight the first row of list B and move it back once they're removed.
>>1126353
You're not wrong but in the real world, data isn't in a folder full of excel docs, data is in MDB's.
Big corporations would go out of business is files were managed excel doc, by excel doc, by excel doc.
Excel is only used to send attachments in emails.
If you work for a big company, you're gonna be in Access or some other database manager, not a spreadsheet viewer.
>>1126372
"In the real world" you're expected to understand that "I'd use Access" isn't an acceptable answer when the questions purpose is to test excel skills.
>>1126388
>Using a program (which I don't actually believe people use because none of my homework for my BA required it) that comes bundled with another program to solve a problem will make me ineligible for a job
ok retard
>>1126328
>have you heard about Ethereum brah? You should just quit your job right now. LMAOing at your life desu senpai.
>>1126328
>select both rows
>use conditional formatting to highlight dups
>filter out all highlighted cells in column B
>copy
>paste values
>go to your house and fuck your wife
This is probably easier than using vlookup even.
I just ctrl + f honestly.
>>1126328
The fuck is a CV? You guys drug test?
> i have excel on my resume
Anyone who says 1 list and remove duplicates won't last a week in finance, you're what I'd call a box checker. Sure you can ensure that there are no duplicates by monitoring the count, but the next question will be: which ones are duplicates? Followed by get me a list where the duplicates are in A, but not B.
Luckily excel is frowned upon at most jobs I'd be looking at -- I'd make 2 quick text files and convert all to lowercase. Then quickly run them through comm to get quick answers to all questions about the 2 data sets
>>1126432
Somebody get this hothead out of here.
OP here
The way I would do this:
>vlookup A vs B
>filter for ones marked by vlookup
>delete them (or save them on another sheet if required)
>unfilter
Takes less than 30 seconds
>>1126457
pivot + filter takes less than 10 seconds
>>1126328
>I use R for that. Much simpler.
>>1126351
Or I can use the OLEDB driver.
I'm actually MSITA certified
I don't remember shit though
>>1126393
I love it when kids like you blather on about access and large companies because your professor told you about it
I would read each list
I have Pajeet do most of my heavy lifting for me. When do i start?
>>1126328
Vlookup and pivot tables since those are the only two things these retard companies care about you knowing. Fuck off mso heroes.
Combine both lists into a text file
$ cat list1.txt list2.txt > list.txt
$ sort list.txt | uniq > newlist.txt
$ rm list1.txt list2.txt
Lel
Easy.
Delete list b you retard
>>1126328
Use a fuzzy-matching procedure to find duplicates as well as near-duplicates, to account for spelling and formatting discrepancies.
Best answser ITT
>>1126393
>i dont believe people use excel because I never have
>Sophomore biz student
>No fucking idea what most this shit means
I'm scared, guiz
>using cucklookup
Who /indexmatch/ here
>>1127657
Only patrician in this thread.
>>1126328
I don't know why any company would care to much about this. If you got the job anyway you could learn how to use excel in like 2 days...
>Oh actually by advanced skills I mean I farm out most of my work to India for 1/20th of my paycheck. I just check it and hand it in
>>1126328
I fucking google how to do it
Realistic answer
I actually aced an interview with this response
Interviewer asked me how I would find out the amount of taxis in New York City if I looked out the window
Quick and easy, fuck making up bullshit for an estimate
>>1126372
>Access
access is the biggest pile of shit i've ever used in my entire life
>>1127035
>tfw I use excel almost every day
>tfw this is the first thing I thought of
>tfw I've used bash to write most of my vba macros because I can't be fucked to learn that stupid shit
http://chandoo.org/wp/2013/01/16/top-10-formulas-for-aspiring-analysts/
Huh, never used Index Match before, must give it a go
>>1126328
>having "advanced excel skills" on your resume
what are you? a janitor?
would kill myself before properly learning excel. because that would mean i would be miserable.
>>1128441
BTFOs Vlookup, no idea why people still use that shit.
>>1126328
not
>code fizzbuzz without using the standard library
pretty poor memeing
>>1130172
>tfw I've actually done this in ARM ASM using hardcoded bitmaps for characters and a framebuffer
>>1130160
Because then you're a Vlookup Crook
>>1126402
Kek
If serious, Curriculum Vitae, but I have trouble believing this isn't a joke.
>pull out my laptop
>google: "excel two lists check if one contains another"
>click on first link
>done
And thats how i did every job in my life.
