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:


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.

now let us breakup the formula and understand the logic in the solution
Step 1:

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:

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

Step 3:

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:

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


5 Responses to Extracting unique records from two columns

  1. I went over this Extracting unique records from two columns anandexcels web site and I believe you have a lot of good information, bookmarked…

  2. Very excellent info can be found on web site.

  3. Warren Apel says:

    That’s an amazing solution! I almost didn’t think it would work. I have 2 sheets, each with several hundred rows. I’m combining them in a really complex way and couldn’t figure out how to get just one person per row. This solved it – thanks!! My adapted formula is
    =ArrayFormula(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE(‘Compare 1′!A:A&CHAR(9),’Compare 2’!A:A&CHAR(9)),CHAR(9)))))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: