Finding matching columns in excel

Posted by fakaff on Super User See other posts from Super User or by fakaff
Published on 2012-06-18T19:23:13Z Indexed on 2012/06/18 21:19 UTC
Read the original article Hit count: 272

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.

© Super User or respective owner

Related posts about microsoft-excel

Related posts about automation