# How do I do this in Excel? **URL:** \ **Category:** Factual Questions\ **Created:** [November 21, 2003, 9:35pm UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577 "2003-11-21T21:35:44Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [November 21, 2003, 9:35pm UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/1 "2003-11-21T21:35:44Z")
I’m struggling with a problem at work, and had the brainstorm to ask my fellow Dopers to act as my tech support: I have two Excel tables, with similar lists: one long list, one short list. I would like to match the two lists, and combine data elements where they match, and leave blanks (on the long list) where they don’t. It would be easy to do this in Access – just a query that outer joins the two lists on their common elements. In Excel, though, I need to be able to create some sort of If statement that says “if column A, B, C and D from worksheet 1 have an equivalent row (same values in column A, B, C and D) somewhere in worksheet 2 then take column F from worksheet 2 and move it to column F in the appropriate row in worksheet 1.” And I have no idea what the right syntax is. Any help? ---
**Author:** ![NBIT33](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@NBIT33](https://boards.straightdope.com/u/NBIT33)\ **Post date:** [November 21, 2003, 10:48pm UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/2 "2003-11-21T22:48:34Z")
Why are we ruling out Access? You could even use Access to import-and-export the Excel, so you could still begin and end in Excel. ---
**Author:** ![Jpeg\_Jones](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@Jpeg\_Jones](https://boards.straightdope.com/u/Jpeg_Jones)\ **Post date:** [November 21, 2003, 10:50pm UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/3 "2003-11-21T22:50:39Z")
Try concatenating together the values from A, B, C and D in both worksheets and using the resulting values for comparison. In other words: If (worksheet 1) A+B+C+D = (worksheet 2) A+B+C+D, then _whatever_, else _whatever_. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [November 21, 2003, 11:38pm UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/4 "2003-11-21T23:38:00Z")
**NBIT33** Because I’m delivering the results through a tool that doesn’t interface with Access. **Jpeg** The problem with this is that the row that matches row 1, worksheet 1 may be row 38, worksheet 2. Worksheet1 is a subset of worksheet 2 (with different values for the same keys). ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [November 22, 2003, 12:02am UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/5 "2003-11-22T00:02:47Z")
You can’t do that with a simple Excel command. It’s not so hard to write a vba function (macro) to do this, scanning the two lists and copyng matching cells as required. It’s easier ifthe lists are sorted the same, but that’s not requried. But if you’re already skilled with Access and clueless about VBA, then I agree with NBIT33: export to Access, do what you want and reimport into Excel. Particularly if this is a one-time activity. ---
**Author:** ![daniel801](https://avatars.discourse-cdn.com/v4/letter/d/b2d939/32.png) [@daniel801](https://boards.straightdope.com/u/daniel801)\ **Post date:** [November 22, 2003, 12:06am UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/6 "2003-11-22T00:06:19Z")
I can help you. Highlight any one of the cells. Go to Data-Filter-Advanced Filter. Choose copy to another location. Pick an empty column for this. For List Range, choose all your data you are interested in removing duplicates for. Leave Criteria Range blank. Choose Unique Records only. There ya go. You may want to copy and paste all your data in one big column to start with. Also, to undo this, go to Data-Filter-Show All. ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [November 22, 2003, 3:29am UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/7 "2003-11-22T03:29:38Z")
Assuming one of the lists has unique values (i.e. no two rows have the same values in all of columns A-D), you can use VLOOKUP on the column with the concatenated values. Eg. If Column E in each worksheet has the concatenated values, and you want to pull column F from Sheet1 to a column in Sheet2, you’d put this formula in the first cell of that column: =VLOOKUP(E1, Sheet1!E:F, 2) and copy it down the column. You can do a lot of database-type work in Excel using VLOOKUP, HLOOKUP, and nested INDEX and MATCH functions. If you’re expecting a lot of complicated work like this, those would be some good functions to play with & get a feel for what they can do. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [November 24, 2003, 9:48pm UTC](https://boards.straightdope.com/t/how-do-i-do-this-in-excel/214577/8 "2003-11-24T21:48:17Z")
VLOOKUP is exactly what I needed. Thanks, all, for your help.