Help with excel!
i need some quick help with excel
lets say i have 2 list of companies
list B has like 2000 more companies than list A
how do you compare two lists and remove the things in list B that are not in list A?
thanks
i need some quick help with excel
lets say i have 2 list of companies
list B has like 2000 more companies than list A
how do you compare two lists and remove the things in list B that are not in list A?
thanks
+278 | Looking to raise a Billion Dollar Fund - College Junior | 41 | 16h | |
+226 | It doesn’t need to be this way | 31 | 2h | |
+179 | What to expect during your IB summer internship and how to secure the full-time offer | 9 | 4h | |
+140 | Biggest gripe in IB: people with no balls | 38 | 5h | |
+137 | Career Bankers, Was It Worth It? | 45 | 14h | |
+89 | RBC M&A vs PJT RSSG | 14 | 2d | |
+81 | I FINALLY DID IT, I GOT A FT OFFER! | 14 | 17h | |
+80 | MD Can’t Unplug | 25 | 1s | |
+63 | Did I mess up by correcting my VP Publicly? | 20 | 1d | |
+57 | BofA Assoc who died had sought to leave, citing long hours | 15 | 2h |
Career Resources
i assume by list A and B u mean columns A and B. lets say your lists start at A1 and B1
in C1 put =COUNTIF(B:B,A1) and then drag it all the way to the last data point. every item in A that is in B will have a 1 next to it. if it doesnt, it will have a 0
now select columns B and C and click Data then Sort
click No Headers, sort by Column C, click Descending
now all the 1s should be on top and the 0s on the bottom. delete all the data points in B next to the Zeros, and the two lists A and B should have the same companies
i did something like this at work today. i just typed this up from memory, hope it works out
thats amazing
another way to do it would be using a vlookup
insert a column next to the shorter list. put a yes next to each name. then insert a column in the longer list and use the vlookup function to to match the names, bringing the yes value over to your longer list for the matches. hard code all the values in the vlookup column. the "yes" values will be your matches. sort and erase the non matches.
Why not throw in a few INDIRECT, REPLACE, SUMIF and some ISERRORs for good measure? Then read the question again.
The answer is to delete list B, copy list A and then paste it where list B was.
not necessarily. list A could have the Fortune 500, List B could have 400 of the Fortune 500 plus another 200 random companies no one's heard of. If you answer op's post correctly, you'd only have the 400 F500 companies left in list B... While the vlookup may not seem the easiest or most intuitive for a simple application like this, it's really useful in a wide variety of situations and familiarity with that function is helpful beyond words
Vero enim qui nihil inventore asperiores accusantium. Qui unde unde quasi tempore fugit quo omnis. Et necessitatibus ut porro odio. Eaque et ratione qui sit voluptates vel aperiam. Fuga porro facilis temporibus repellendus perferendis quos harum.
Cupiditate illo aliquid ea est. Omnis dolores quas voluptas fugit deserunt. Doloremque omnis cumque et quis porro.
Quis minima quas autem aut. Blanditiis qui deserunt eos eius magnam aliquid. Ratione quasi alias aut dicta quisquam.
Ratione et consequatur nulla. Quos nisi impedit ducimus consequatur consequatur et. Iure non quos voluptatem deserunt sit tempora distinctio et. Et et ipsam ipsum eum pariatur et aperiam excepturi.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...