Compare multiple columns, pull out only cells that appear in every column

Question

I have 10 or so columns in my worksheet. Each column contains about 200 names, and there is no other data on the sheet.

What I'd like to do is create a new column that only contains the names that are common between the columns. So essentially compare each cell in each column to all the other cells in all the other columns, and only return the the common cells.

For example:

Column1 : name_A, name_C, name_F

Column2: name_C, name_B, name_D

Column3: name_C, name_Z, name_X

So in this example, the new column would only contain name_C, because it's the only value common to all three columns.

Is there any way to do this? My knowledge of Excel is quite poor, and I can't find anything similar to my problem online so I would appreciate any help.

Thanks for reading,

N

Answer 1

Put everything on a single spreadsheet and create a pivot table is probably more efficient than the algorithm you have on your mind.

Answer 2

here is my mock-up. I added extra names to demonstrate better

D(formula) has the easiest version. this will list only values that appear in all columns, but these will appear on the same lines as the corresponding name in column A, with blanks, and not sorted (giving D(result))

IF you would like all the names to appear the the top - as shown here in column E you can either sort your table (you will have to re-sort if the columns change) OR you can use my solution below:

• get yourself the MoreFunc Addon for Excell ( here is the last working download link I found, and here is a good installation walk-through video )
• once all is done select cells E1:E8, click the formula bar and type the following: =UNIQUEVALUES(IF(COUNTIF(A2:C9,A2:A9)=3,A2:A9,""))

• accept the formula by clicking ctrl-shift-enter (this will create an array-formula and curly braces will appear around your formula)

          A       B       C       D(formula)                              D(result)       E(result - sorted)
      -------------------------------------------------------------------------------------------------------
  1   |   name_A  name_C  name_C  =IF(COUNTIF($A$1:$C$8,A1)=3,A1,"")                      name_m
  2   |   name_C  name_B  name_Z  =IF(COUNTIF($A$1:$C$8,A2)=3,A2,"")      name_C          name_C
  3   |   name_F  name_D  name_X  =IF(COUNTIF($A$1:$C$8,A3)=3,A3,"")
  4   |   name_t  name_o  name_g  =IF(COUNTIF($A$1:$C$8,A4)=3,A4,"")
  5   |   name_y  name_p  name_h  =IF(COUNTIF($A$1:$C$8,A5)=3,A5,"")
  6   |   name_u  name_k  name_7  =IF(COUNTIF($A$1:$C$8,A6)=3,A6,"")
  7   |   name_i  name_5  name_9  =IF(COUNTIF($A$1:$C$8,A7)=3,A7,"")
  8   |   name_m  name_m  name_m  =IF(COUNTIF($A$1:$C$8,A8)=3,A8,"")      name_m