How to retrieve unique rows by an index column in Google Spreadsheet?

Question

Suppose I have the following Google Spreadsheet:

id1 mkdmc   cdjnc
id2 cn39    cncn
id3 cndc    cncn
id1 3mc     cm

And I want to transform it into:

id1 mkdmc   cdjnc
id2 cn39    cncn
id3 cndc    cncn

So to remove the row that has a duplicated id1 in column A. Note that the row isn't fully duplicated, just the value in column A.

How would I go about doing this with a formula?

Answer 1

This could be simplified by using curly brackets {} within the vlookup to indicate which columns we would like to return. For example - 2 columns:

=arrayformula(vlookup(unique(A:A), A:B, {1,2}, false)

3 columns:

=arrayformula(vlookup(unique(A:A), A:C, {1,2,3}, false)

This can then be easily expanded for any number of columns and the user can choose which columns they need eg: {1,2,5,7,9,10}.

Answer 2

={unique(A:A), arrayformula(vlookup(unique(A:A), A:B, 2, false))}

Explanation:

• A table of 2 columns is constructed in a single formula (expanding to 3 columns is left as an exercise to the reader)
• The first column is the list of unique IDs
• The second column looks up the 1st value by that id in the second column of source