To display only unique values from two columns

[manish.agrawal446@gmail.com]

HI,

I have two columns with multiple values. Some values can be repeated in the same row or the different row. We need to find the values which are present in either column only once..

If it is repeated more than once at any place it should be ignored

 

[NARAYANK991]

Hi Manish ,


You can use Conditional Formatting to color those cells which have unique values.


The principle is that the COUNTIF function returns the number of occurrences of a value in a range ; so , if your values are in column F and column G , a value is unique if it occurs only once in one column , and does not occur at all in the other column.


Thus , entering a formula such as :


=AND(COUNTIF($F$3:$F$21,F3)=1,COUNTIF($G$3:$G$21,F3)=0)


and selecting a Fill color for the format , will color the cell if the value in it is unique ; I have assumed that your values are in the range F3:G21.


Apply the above formula to the relevant cells in column F.


For column G , use the formula :


=AND(COUNTIF($F$3:$F$21,G3)=0,COUNTIF($G$3:$G$21,G3)=1)


If you are using Excel 2007 and later , even simpler is to select the two columns , select Conditional Formatting , New Rule , Format Only Unique or Duplicate Values , Select Unique , and select the Fill Color.


Narayan

 

[Debraj]

Hi Manish,


In respect with NARAYANK991,

You can select both the columns (F & G).

Go to Conditional Formatting and enter formula as..

=COUNTIF($F:$G,F1)=1

Select any Format / Color

It will format your all the unique values, comparing both the columns.


If you are using Excel 2007 and later , even simpler is to select the two columns , select Conditional Formatting , New Rule , Format Only Unique or Duplicate Values , Select Unique , and select the Fill Color.

 

[manish.agrawal446@gmail.com]

I don't require formatting where as I require all the unique values to get displayed in 3rd coloumn

 

[NARAYANK991]

Hi Manish ,


With reference to my earlier post , put in the following array formula ( entered using CTRL SHIFT ENTER ) in H3 , and copy it downwards as far as possible ; Excel will populate the column H ( from H3 downwards ) with the unique entries from columns F and G , and thereafter , the remaining cells will display blanks.


=IFERROR(IFERROR(INDEX($F$3:$F$21, MATCH(0, COUNTIF($H$2:H2, $F$3:$F$21), 0)), INDEX($G$3:$G$21, MATCH(0, COUNTIF($H$2:H2, $G$3:$G$21), 0))), "")


Narayan


P.S. Taken from : http://www.get-digital-help.com/2009/06/16/extract-an-unique-distinct-list-from-two-columns-using-excel-2007-array-formula/

 

[bobhc]

Good day Manish

Take a look at this workbook https://dl.dropbox.com/u/75495784/Compare2Lists_1.xls


here's the explanation...


Column A

Contains List1

Column B

Contains a formula: =IF(ISERROR(MATCH(A2,E:E,0)),0,1)

If we find the first element of list 1 in list 2, then return 1 otherwise, return 2

Column C

Adds a random number to Column B

We do this so that we can pick out the unique values later

Try removing this random number from the formula, and look at the result

Columns E F G

Same but for List 2

Column I

Sorts List 1 descending by our random number column C

The ones where we don't have the element from list 1 in list 2 will fall to the bottom.

Column J

Removes anything from list 1 that is not in list 2

Column K

An sorts column J alphabetically.

Now you have a sorted list of what is in list 1 and not in 2

Column L

Sorts List 1 ascending by our random number column C

The ones where we do have the element from list 1 in list 2 will fall to the bottom.

Columns M and N are the same as before - removing the elements, and sorting.

Columns O, P and Q are similar to above, but for list 2

Columns S T and U essentially remove the "zzzzz" from the other columns