UNIQUE FUNCTION

UNIQUE

Unique function is unique to google docs spreadsheets, the Returns only the unique rows in the source array, duplicates will be ignored

The syntax of the function is as follows:

UNIQUE(SourceArray)

SourceArray:  range of cells, number, date or string, data has to be of the same type, it can also be array formula results.

Note:

  • The output of the unique function will in the same order as that of SourceArray.

  • unique values will give only for vertical array only

  • to get unique values from a horizontal array we have to use the transpose function inside the unique function.

Example 1:



the data is cell A2:A11 to extract the unique value from the formula used in cell B2 is

=unique(A2:A1)

Example 2:


we can extract the unique values from column A&B the formula in cell C2 is

=ArrayFormula(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE(A2:B&CHAR(9));CHAR(9)))))

the explanation to the above formula at the following link

https://anandexcels.wordpress.com/2012/02/14/extracting-unique-records-from-two-columns/



Advertisements

Extracting unique records from two columns

Question  from sadistiko
I have two columns:

A              B
Mario       Alen
Dario       Dario
Mario       Alen
Marko    Marko

I want in column C get only unique names from A and B
If I use =UNIQUE this will again create C and D columns, but I need it all in one column. How to do it?

C would look like this:

C
Mario
Dario
Marko
Alen

Solution:
If we try to do this using excel it will ugly big array formula, but the same can be achieved in google docs spread sheets without much fuss and very easy to understand.

I have tried with various formula but i could able to figure it out. Adam Lusk  Top Contributor in the Forum has came up with the following solution.
=ArrayFormula(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE(A:A&CHAR(9);B:B&CHAR(9));CHAR(9)))))

now let us breakup the formula and understand the logic in the solution
Step 1:
=ArrayFormula(CONCATENATE(A:A&CHAR(9);B:B&CHAR(9))

the whole data in cell A1 to B4 has been converted into text in the single cell separated by special character char(9) which is nothing but horizontal tabulation.

Step 2:
=ArrayFormula(SPLIT(CONCATENATE(A:A&CHAR(9);B:B&CHAR(9));CHAR(9)))

in the above picture we have divided the single cell text to multiple cells using split function.

Step 3:
=ArrayFormula(TRANSPOSE(SPLIT(CONCATENATE(A:A&CHAR(9);B:B&CHAR(9));CHAR(9))))

we have transposed the horizontal array into vertical array by using the Transpose function. the result will as show in the above picture.

Step 4:
=ArrayFormula(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE(A:A&CHAR(9);B:B&CHAR(9));CHAR(9)))))

in the final step we will get required results using the unique function in Google doc.

i would like thank Mr Adam Lusk for the contribution