How do I do this in Excel?

Factual Questions
1

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?

2

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.

3

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.

4

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).

5

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.

6

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.

7

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.

8

VLOOKUP is exactly what I needed.

Thanks, all, for your help.