Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I don't know how to properly describe what I need to do, so I will give an example. A colleague has a data set in Excel like so:

Col A    Col B    Col C
aaaaa    aaaaa    bbbbb
bbbbb    ccccc    ccccc
ccccc    ddddd    eeeee

The end result should be something like this:

Col A    Col B    Col C
aaaaa    aaaaa
bbbbb             bbbbb
ccccc    ccccc    ccccc

Or even:

      Col A    Col B    Col C
aaaaa Yes      Yes      No
bbbbb Yes      No       Yes


(if it helps, the columns are protein extraction methods and the letters are protein IDs - we need to determine which proteins are extracted by which methods)

My colleague is doing this by hand, but there is enough data that it would be really helpful to automate it.

Is there a formula in Excel to do this automatically?

share|improve this question
Can we assume, that each input column is sorted (sorting doesn't need to be alphabetical, but must be the same for all columns)? – Adam Ryczkowski Sep 26 '12 at 7:54
Is number of columns (=3) fixed? – Adam Ryczkowski Sep 26 '12 at 7:55
@AdamRyczkowski I believe the input column is sorted alphanumerically. The number of columns is not fixed (the file my colleague showed me has 5). – Lenna Sep 26 '12 at 18:06
It is possible to do it entirely with formulas. It will not be that easy as Scott's method. Because it will be done in formulas, the result will be 100% automated so there will be no need for any mouse clicks - only change input data. If you are interested let me know and I'll post it. – Adam Ryczkowski Sep 26 '12 at 20:36
@AdamRyczkowski: I have found formulas to get unique values from multiple columns, but I think copy and pasting will work well enough. – Lenna Sep 28 '12 at 18:06
up vote 3 down vote accepted

This is not a “turn-key” solution, but if you have thousands of rows, this may save you some effort.  (Do this in a scratch copy of your file, just in case something blows up or melts down, because “Undo” doesn’t always work.)  Note: this procedure was developed for Excel 2007.

First, copy all your data into a scratch column; let’s call it V.  Note that you must copy the heading from Column A, or else put some dummy value in cell V1.

Illustration of copying data

Now go to the “Data” tab, “Sort & Filter” group, and click on “Advanced”:

                        “Sort & Filter” group, with “Advanced” highlighted

This will bring up the “Advanced Filter” dialog box:

                  “Advanced Filter” dialog box

Verify that “List range” shows your data in Column V.  Select “Copy to another location” and “Unique records only”.  Type “W1” in the “Copy to” field — or click in the field, and then click in W1 (there are several techniques that will get the same result).  Click on “OK”.  You should get something like this:

                              Data from V sorted into W with duplicates removed

Now enter =NOT(ISNA(VLOOKUP($W2,A$2:A$5,1,FALSE))) in X2, and drag/fill.

enter image description here

Delete column V, and fix the headings (in Row 1) at your leisure.

share|improve this answer
This looks great, I think we will try it. – Lenna Sep 26 '12 at 18:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .