Match 2 columns

Posted by A different Scott on December 28, 2001 1:04 PM

I'm a newbie, apologies in advance for any lack of clarity.

How do I make 2 columns match on adjacent cell strings, where 1 column is a subset of the other? Here's an example, calling one column "master" and the other "subset":

Master Subset
Biology Anatomy
Anatomy Zoology
Zoology Chemistry
Physics
Chemistry

What I want is this:

Master Subset
Biology (empty cell)
Anatomy Anatomy
Zoology Zoology
Physics (empty cell)
Chemistry Chemistry

Obviously I can do this by eyeballing it and inserting or moving cells, but would like to automate it as there are over 300 cells to match on about 30 worksheets, and the subsets vary. Sorting and deleting cells is not an option - each worksheet must have the complete list.

Any suggestions? Thanks for the help, and Happy New Year!

Posted by IML on December 28, 2001 1:13 PM

Insert a column between A and B so your master is A, subset in C.

In B1 put this formula
=IF(COUNTIF(C:C,A2),A2,"")
and copy it down.

Good luck

Posted by A different Scott on December 28, 2001 1:23 PM

Thanks IML, but I neglected to include another piece of the puzzle. There are 10 additional columns to the right that must include blank cells where they don't match as well, e.g.

Master Subset Year1 Year2 etc.
Biology (blank) (blank) (blank)
Anatomy Anatomy (value) (value)
etc.

Sorry about not including that info.

Posted by IML on December 28, 2001 1:40 PM

How about vlookup?

Okay lets say your subset is in range n1:W10 and master list is A2.

How about
=IF(COUNTIF($N$1:$N$10,$A2),VLOOKUP($A2,$N$1:$W$10,COLUMN()-1,0),"")
in B2 and copy that down and over 10 columns?

Posted by A different Scott on January 03, 2002 11:31 AM

Re: How about vlookup?

This works exactly as I'd hoped. Thanks a ton for the assistance, I sure do appreciate it and the fact that there's a lot of potential that I'm not tapping yet...