Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've never used excel before so I need the simplest solution available, and this is a work assignment due this week so I didn't have time read much of the documentation.

Basically, I have two tables, A and B, and they are both thousands of rows long.

Description of my task: right now (since I don't know better) I'm manually doing this:

  1. Go to row i in table B.
  2. Select entries in columns B(a, b, c) of that same row.
  3. Look for a row in table A where column A(b) matches row B(a).
  4. Paste the entries of columns B(a) of row i at the end of the row found in the last step.
  5. Repeat for row i + 1.

Example: row B(cat, dog, mouse) matches A(mammal, cat, Mr. Whiskers). So I would paste B after A and have A(mammal, cat, Mr. Whiskers, cat, dog, mouse).

Note: I am not joining tables. I am merely extending table A by pasting row A(b) if row A(b) matches row B(a).

Also, sometimes entries are spelled slightly differently. Using wildcards to search for candidates would be of help.


As the description should let on, this task is very tedious and inefficient if I don't know how to automate some operations (there are thousands of entries).

Any quick tips as to how to be more productive is a big help.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

The answer is, arguably, one of the most powerful functions you can create in excel. If you're new to excel, this is going to be a lot to swallow.

Given your exact example, you may be able to get away with using VLOOKUP. But I'm assuming the reality of the situation is a bit more complicated than your example so I'm giving you something that can handle pretty much any situation. It's a combination of the INDEX function and the MATCH function.

I created two tables:

TableA:Row1(mammal, ferret, Mr. Whiskers)

TableB:Row1(cat, dog, mouse)

TableB:Row2(ferret, iguana, turtle)

TableB:Row3(horse, cow, pig)

In TableA:ColumnD I put the following formula:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),1)

First, lets look just at INDEX. INDEX takes 3 arguments, the range you want to search over, the row of the value you want, and the column of the value you want. The range you want to search over is specified by TableB!$A$1:TableB!$C$3.

TableB! says go to another sheet named TableB

The dollar signs in front of the column and row say "these are absolute, do not automatically increment when filling the formula down (or right, left, up)"

The 1 at the end says "the first column in the range" which in our case happens to be A and contains cat, ferret, and horse.

Now, to get the row, we need to use another function called MATCH to match a value from the row we are currently working on. MATCH also takes 3 values. The first is the value you are searching for, the second is a single column to search in, and the final is how to match (in this case we way FALSE for "exact match").

We want to match the value that is in TableA:ColumnB for the current row. So the first value is $B1. Which says the column B is absolute, don't increment it. But the row should increment to match whatever row we are on when we fill down so it does not have the preceding dollar sign.

The column to search in is the A column on TableB and is absolute so it won't increment.

If we then go to TableA:ColumnE and put in:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),2)

And in TableA:ColumnF we put:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),3)

We get the desired result. Notice the only difference in the formulas is the final number specifying the column to return.

If we change TableA:ColumnB to "ferret" then Column D changes to ferret, Column E changes to iguana and Column F changes to turtle.

Finally, select the cells in row 1 under D, E, and F. Drag your mouse down to cover all the rows necessary and press Ctrl+D which will fill the formula down through all the rows, incrementing the search term in the MATCH function to search for the value in column B of each row.

Further reading:

http://www.mrexcel.com/articles/excel-vlookup-index-match.php

share|improve this answer
    
Thanks, I'll see if I can figure out how to use that. These files are an absolute nightmare though: spelling errors every other entry, duplicate rows, duplicate columns that sometimes are different for no rhyme or reason, rows that are almost the same except for some minor difference so it's impossible to tell which is the right one... is there an excel function that shoots me in the face? –  iDontKnowBetter Jun 18 '12 at 20:49
1  
Excel doesn't have a built in function for that. You need to write some VBA code to return the username. Nothing's easy. :P –  embedded.kyle Jun 18 '12 at 21:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.